Comments on: Excel substring: how to extract text from cell

Although there is no such thing as Substring function in Excel, there exist three Text functions to extract text of a given length from a cell. Additionally, there are FIND and SEARCH functions to get a substring before or after a specific character. Continue reading →

Comments page 3. Total comments: 297

  1. Hello!

    I'd like to extract a string of text that occurs between the first "_" and the 5th "_" from the right (after "ztt_" and before "_dev_rev_vX_icr_tt", where X is a changing version number)

    For example -
    Cell: ztt_tool_vacuum01_dev_rev_v3_icr_tt
    Extract: tool_vacuum01

    Another example -
    Cell: ztt_first_mom_hair01_col01_dev_rev_v9_icr_tt
    Extract: first_mom_hair01_col01

    Another example -
    Cell: ztt_mop_def01_col01_dev_rev_v4_icr_tt
    Extract: mop_def01_col01

    Could you help? Thank you :)

    1. Hello!
      Please check the formula below, it should work for you:

      =MID(A1,SEARCH("_",A1,1)+1,SEARCH("_dev_rev",A1,1)-SEARCH("_",A1,1)-1)

      Hope this is what you need.

      1. This worked wonders! Thank you kindly :)

  2. Hi,
    Very nice article.
    I want to get the result following text string:-
    P08LREMTNM172// 10.139.131.69-LTS-MTN-MSAG25CANALBANK2-A-M result is canal bank
    P08LREFZDM090//Neshaman Park Awan Market Ferozpur Road 10.139.97.146 result is neshaman park
    P08LREGBGM101//C-51 Hafeez center Gulberg 10.139.82.198 result is hafeez center
    P08LREARDM064//10.139.130.166-LTS-ARD-C15BTYPEFLAT-A-M result is c15bty
    P08LREMTNM065//10.139.131.14-LTS-MTN-065HanjarwalChowk-A-M result is Hanjarwalchowk
    P08LREGNRM025//10.139.114.30-LTS-GRI-C2160feetRoad-A-M result is c2160feetroad
    P08LREGNRM018//10.139.114.130-LTS-GRI-C19JaffriaColony-A-M result isc19jaffriacolony

    P08LREMALM054//10.139.64.86-LT-LHR-MSAG14sunderIndustrialstateMAL-Z-M result issunderindustrial estate
    P08LREASLM050//MSAG-1 Central Park FZRD 10.139.47.110 result is central park
    P08LREFZDM024//10.139.115.14-LTS-FZR-C25niaziachkFZR-A-M result isniaziachk
    P08LREFZRM085//MSAG-51 -Qanchi Main Bazar near Batul Islam Madrassa- FZR 10.139.97.126 result is Qainchi main bazar
    P08LREJTNM020//C-29 Near Bank Lalazar Colony Phase-II (Riawind Road) Lahore -10.139.78.134
    P08LREGNRM017//10.139.114.98-LTS-GRI-B4ChubarjiparkGRI-A-M
    P08LREMRDM008//C-35 Near Ilyas Autos Saidpur Multan Road -10.139.77.158

    and so on
    I will be very appreciate your great help..
    Thanks,

    1. Hi!
      To write a formula to extract a string from text, your data must have a common pattern and be consistent. I don't see it here.

  3. Hello!
    I want to extract the date from this text message:
    Overdue for unfinished orders as of: 16-11-2019

  4. Hi,

    I want to extract the just the website address and not the rest of the links. Can you help me with the formula?

  5. Hello,

    Are you able to extract the last bit for me, i.e. '1453510'

    /CODE/ZZ/PARIS-LONDON/89853323/1453510

    Many Thanks,

    2. =RIGHT($B2,LEN($B2)-SEARCH("/",$B2,LEN($B2)-8))

  6. Hi,

    If I want to extract just the name, what formula to use?

    Farms Limited #33-1A

    thanks,

    1. Hi!
      Try the following formula:

      =LEFT(A1,SEARCH("#",A1)-1)

      You didn't explain, but I'm guessing the name will be retrieved.

  7. Hello Ablebits Team,
    I have some data that I need to extract but can't seem to find the answer above, this is one of the cells that I need to extract a Product ID from (all cells will have varying numbers of characters) -

    "adult=No,age_group=Adult,c2c_barcode=4062451243197,c2c_colour_swatch=16402,c2c_excerpt=The classic design of the Puma Road Map Golf Polo Shirt is simple enough to pair up with any golf trousers or shorts yet stylish enough to get you through your round with confidence. ,c2c_features=
    dryCELL moisture-wicking technology
    UPF 40 UV protection
    Stretch fabric
    Puma branding
    80% Polyester / 15% Cotton / 5% Elastane
    ,c2c_mpn=595788-06,c2c_noaddtocart=0,c2c_rrp=40.00,call_for_price=No,color=Ibiza Blue/Dark Denim (PU595788-06),cross_over_id=PU59578817,default_price_of_set=0.000000,description_below_images=0,ping_popup=No,pl_size_chart=Puma Men's Clothing,price_per_unit=0,product_tag=Sale,size=S,size_system=UK,size_type=Regular"

    Basically, all of the cells will include the cross_over_id= but what I need to extract is the actual cross_over_ID - in this instance it is PU59578817, is there a formula for this, please?

    1. Hello!
      The formula below will do the trick for you:

      =MID(A1,SEARCH("cross_over_id=",A1)+14, SEARCH(",",A1,SEARCH("cross_over_id=",A1)+14) - (SEARCH("cross_over_id=",A1)+14))

      1. Alexander, you are an absolute legend! Thank you.

  8. Hello,

    I have a document coding which will be in the same format which has 3 Hyphen and 2 space and 1 dot (occasionally)

    Eg for max lenght of textstring is: AAA-(BBB)-C99-DD 5555.001 (XX-YY)
    Eg for min lenght of textstring is: AAA-(BB)-C8-DD 5555 (XX)

    I need to print each section in the coding to a different cell. For example;

    A1: AAA
    A2: (BBB)
    A3: C99
    A4: DD
    A5: 5555
    A6: 001
    A7: (XX-YY)

    I couldn't sort it out the formula for "DD" section after last hyphen and before space and the remaining text. Thanks in advance.

    1. Hello!
      Your string is too long to be split using a formula. It also has no common pattern. Do I recommend using the Split Text tool, as described in this guide. Separate by "-" first, then by other separators. Use the Transpose tool to write the result to a column.
      You can try them in trial mode.

  9. column of data with either numbers of varying length or numbers of varying length followed by a letter. I need to split this column into 2 columns. If the cell is a number then the 1st column should simply return the number and the 2nd column will remain empty. But if the cell contains any text the 1st column will return the numbers from the cell and the second column will return any letters. Thanks so much for any help.

    For instance

    Col A Col B Col C
    671 671
    894256 894256
    89G 89 G
    9451w 9451 w
    4589 4589
    12tw 12 tw

    1. Hello!
      Use the formula from the article — How to get number from any position in a string.
      To extract all letters except numbers from a cell, use the formula:

      =SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))),MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1),"")))," ","")

      I hope my advice will help you solve your task.

  10. 1-356039-1-1-11771232_82253500
    Hello,

    I am trying to extract the cell right of 1-356039-1-1-, but I can't seem to get it work.

    Any tips?

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =MID(A1,LEN("1-356039-1-1-")+1,50)

  11. I am having difficulty extracting the date listed in several items. I was only able to successfully extract the date (example 2021_02_23) when there was only one underscore proceeding it using =IF(LEN(A4)-LEN(SUBSTITUTE(A4,"_",""))=4,LEFT(RIGHT(A4,LEN(A4)-INT(FIND("_",A4)-1)-1),INT(FIND("_",RIGHT(A4,LEN(A4)-INT(FIND("_",A4))))+5)),"not in correct format")
    Any feedback would be greatly appreciated.

    BIO_2021_02_23_Apple
    BRIEF_2021_09_28_Cat
    MR_TY_2020_03_20_Apple
    TAX_REC_2021_06_29_Bolton

    1. Hello!
      I recommend splitting a cell using any of the methods described in this article. Delimiter - "_". Then use the DATA function.
      If you want to extract date as text use this formula:

      =SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)),MID(A1,ROW($1:$94),1)," ")))," ","_")

      You can replace the "_" character with any other character in the formula, for example "."

  12. Hi,
    I have following texts in separate rows
    TA/21-22/0001
    TAS/21-22/0001
    WH/TA/21-22/0001
    I would like to extract the text before 0001 in each cell with a single formula. Kindly help.

    1. Hello!
      The formula below will do the trick for you:

      =LEFT(A1,SEARCH("0001",A1,1)-1)

      This should solve your task.

  13. below formula in your above example is not working, tried a lot.
    it should extract characters between 2nd and 3rd hypen.

    output should be 3333

    1111-2222-3333-4444

    =MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))-1)

    could you please check why the desire output not comming.

    Thanks a lot

  14. Hello,
    I've got multiple columns each with data like so "a2b3c1"
    eg A1: a2c1
    B1: a3
    C1: z2a4b5c1

    Only one digit will ever follow a letter

    What would be a formula to find and then sum the digit after "a" (eg 2+3+4=9)?

    I hope this makes sense

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(--MID(A1:A4,SEARCH("a",A1:A4)+1,1))

  15. Hello,

    I have a plea at you I have output from database looks like this:
    virtual_eth_adapters=2/0/90//0/0/SWITCH//all/none,9/0/90//0/0/SWITCH//all/none

    to create a working text for further processing I need to get into this format. Basically, add two backslashes and two " to separate two parameters and add backslash \ and quotation mark " at the beginning and end of text.

    \"virtual_eth_adapters=\"\"2/0/90//0/0/SWITCH//all/none\"\",\"\"9/0/90//0/0/SWITCH//all/none\"\"\",
    Any idea?

    1. Hi,
      I found a solution to do it in three parts:
      add characters to the end of string
      change characters in the middle of string
      and update the starting element.
      here are my functions, however, I am not able to combine them into one big nested function
      end of string: =(REPLACE(C32;LEN(C32);1;"e\""\""\"""))
      middle of string: =(REPLACE(C32;FIND(",";C32);1;"\""\"",\""\"""))
      beginning of string: =(REPLACE(C32;1;21;"\""virtual_fc_adapters=\""\"""))
      Thank you for any help

  16. I need to retrieve the text with two conditions

    MS16-057: Security Update for Windows Server 2012 R2 (KB3156059)

    condition 1 : MS16-057
    condition 2 : Windows Server 2012

    Need print this (KB3156059) separate cell if above condition is statisfied.

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Use paragraph: "How to find substring in Excel" and "How to extract text after character"

  17. cell 1: UPENDRA C. UPRETI (6710)
    cell 2: B.S.ARUNACHALAM (6503)

    Dear Sir, i want to extract (only name i.e text) not the extension no.

    1. Hello!
      To extract only text without brackets from a cell, use the formula

      =SUBSTITUTE(SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER(--MID(A1,ROW($1:$93),1))),MID(A1,ROW($1:$93),1),"")))," ",""),"()","",1)

      This should solve your task.

  18. Hello, I have trouble finding a suitable formulas to merge a piece of text from two cells into one cell.

    cell A1: RU_2017_4 DHL Automotive, IČO: 05164581
    cell B1: s.r.o. (EX-04577)

    the result should be: DHL Automotive, s.r.o.

    Thank you!

    1. Hello!
      The formula below will do the trick for you:

      =MID(A1,SEARCH(" ",A1,1)+1,SEARCH(",",A1,1)-SEARCH(" ",A1,1)+1)&LEFT(B1,6)

      Hope this is what you need.

  19. I need to exctract the string {SYS:}}{S:{MAN:CFS FILTER}{SIG:210601AMH_STP431259368}} from

    {1:F01CFSMAU2SAXXX0908456442}{2:O0811400210531EAEAXXXXGXXX00363006232106010000S}{4:{202:0001}{203:0001}{305:A}{332:000000000000}}{5:{CHK:1853274C0ADA}{SYS:}}{S:{MAN:CFS FILTER}{SIG:210601AMH_STP431259368}}

    would some one be able to assist?

    1. Hi!
      If I got you right, the formula below will help you with your task:

      =MID(A2,SEARCH("{SYS:}",A2,1),200)

      Hope this is what you need.

  20. Hi, i have a query that i have a vehicle's data where different models are mentioned in each line of a same company and i want to extract exactly those.

    1 UNIT(S) USED CAR(S) 2017 MITSU MINICAB CH/NO:
    1 UNIT(S) USED VEHICLE MITSU EK WAGON CHASSIS NO :

    these are data in 2 different lines and i want in next column that it should show me either Mini cab or EK Wagon

    1. Hello!
      It is possible to extract the name of the car from the text by the mask. The name of the car must be enclosed in some characters. Or there must be some pattern in the text. I don't see any of this.

  21. What formula we can use to take a number in line after a word
    Ex Biscuit 500
    Biscuit 600
    Biscuit 700
    I want only numbers to taken in different coulum
    As I mentioned numbers which is Available after common word biscuit

    1. Hello!
      The formula below will do the trick for you:

      =SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

  22. I have a state data... The template
    An Anambra state
    Be Benue
    FC Federal capital Territory
    Cr cross River state
    EJ Ekiti
    And I want to extract the state name only..
    Anambra
    Benue
    Federal capital Territory
    Cross River
    Ekiti
    How can I do this

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =SUBSTITUTE(MID(A1,SEARCH(" ",A1,1)+1,50),"state","")

      I hope it’ll be helpful.

  23. Hello,
    I'm working on a proyect that requieres to extract a 4 digits code from the raw data, I found it out using the formula: =MID(E2,FIND("-",E2)+2,4) and it worked perfectly for this template:
    Akbar, Bethany - w4x1 ^
    Acosta, Sandra - m9ii
    Slate, David - h3as
    Sheppard, Jilliam - J0LW ^

    The formula returns 4 digits code after "-" and " " (Space)... I got w4x1, m9ii, h3as, J0LW...
    But did not returned the correct code on othis case:
    Baker-Hudson, Alexis - j2mg

    The reason is because this template contains 2 "-" insted of one.
    What would you suggest? Is there any way I get get all of them right? I'll highly appreciate your help.

    1. Hello!
      The formula below will do the trick for you:

      =MID(SUBSTITUTE(E2,"-","#", LEN(E2)-LEN(SUBSTITUTE(E2,"-",""))), FIND("#",SUBSTITUTE(E2,"-","#", LEN(E2)-LEN(SUBSTITUTE(E2,"-",""))))+2,4)

      I hope my advice will help you solve your task.

  24. Is it possible to search for either of 2 substrings? I.e. if the text contains Orange OR Blue? I'm using this as a baseline to find one substring:

    =IF(ISNUMBER(SEARCH("1zz", A2)), "Yes", "")

    But, I need to see if another option exists as well. Either substring would be a positive result in my case.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(OR(ISNUMBER(SEARCH("1zz", A2)),ISNUMBER(SEARCH("2zz", A2))), "Yes", "")

      I hope it’ll be helpful.

  25. How can extract "Omkarbhai" bhai in first column and second column "Omkarben" how can extract ben,

    formula will apply one column. So it is possible or not. please share a logic please.

  26. How to get selected characters from string like below
    if i need to get 3rd & 5th say "RE" character from below how will i get
    E9LRHE1719

    1. Just correction
      3rd & 5th are LH

  27. Hi,

    I would like to extract the text AFTER the second hyphen eliminating the any space to show just the text (SS & Medicare tax) from the string below:

    5200 · Payroll Taxes:5260 · SS & Medicare tax

    Thanks in advance!

    1. Hello!
      Determine the position of the second hyphen using the formula

      =SEARCH("· ",C1,SEARCH("· ",C1)+1)

      Then use this position in MID function

      =MID(C1,SEARCH("· ",C1,SEARCH("· ",C1)+1)+2,100)

      1. Awesome, thank you!

  28. ASCN12-1Z-CD8,BHFT

    Hello All,

    I need to extract "CD8", please anyone able to help me on this?

    Thanks

  29. i didn't got what i am looking
    i wants to extract number from a string
    i have extracted text to columns
    my name is gopal sharma and i my mobile number is 0011223344)* with me
    your name is unknown and 0011223345) is your contact number
    number could be in any cell
    how could i extract if i have different numbers

    1. Hello!
      To extract all numbers from text, use the formula

      =SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

      I hope my advice will help you solve your task.

      1. Hi,

        Thank you for your sharing. I used this formula and it's work! It's so helpful.
        But there's something problem that I found in my case. That is, how if I want extract numbers from text but with the decimal ?
        for example, I want extract: "ABCD 19.50PQR XTV" and "DCBA GHT KLM 99.90HV"
        should be: "19.50" and "99.90"

        Please help me. Thank you

        1. Hi!
          To extract a number with a decimal separator from text, try this formula:

          =TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=".",".","")))

          You can learn more about how to extract number from text string in Excel in this article on our blog.

  30. How do I extract a specific character from a word?
    eg - if the word is wate%r, how do i extract "%" from it. So if there is a list of words of different length, and different placements of "%", I need one formula which will extract the "%" from it. Result should be "%" for all words.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(ISNUMBER(FIND("%",A1,1)),"%","")

      Hope this is what you need.

      1. Thank you! this worked..

  31. I want to put first four alphabets in last in the same column.
    For Example want to change 4296HR55U to HR55U4296.
    Pls help.
    Regards

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =REPLACE(D1,1,4,"")&LEFT(D1,4)

      I hope this will help

  32. Hi Anyone can help me out extract "AHU" from "LEGTAIFIYA TVS AHU-02-A-1M"

    1. Hello!
      The information presented to you is not enough to give you advice.
      Need to determine if "AHU" appears in the text? Or do you need to extract text from some position?
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  33. May I know how to extract 黃惠玲 from string "Wong Wai Ling (黃惠玲)" in cell A1?
    Thanks & Best Regards,
    Terry

    1. Hello Terry!
      If I understand your task correctly, the following formula should work for you:

      =MID(A2,FIND("(",A2,1)+1, FIND(")",A2,1) - FIND("(",A2,1)-1)

      I hope this will help

  34. I am trying to figure out what formula I would need to use to extract a value from the middle of a string. The length of the value can change so I was looking for a function that allows me to look up between 2 different characters including a character that repeats. An example: What is the area of a 60 degree circle when the bending radius is 1/8 round to the nearest tenth.
    I want to extract the 1/8, what formula would I need to use

    1. Hello Norma!
      I think that according to your requirements it’s impossible to make a formula. The fact is that in the middle of the line is also the number 60. In addition, the character "/" is not a number.

  35. Hello all, really need your help guys!

    I have a series of columns, for example: Colummn with 3 digit numbers like 420, a column with a word and a number like "Nascar 2", and a column with just a word that says "Honda". I need to make a formula that would combine those 3 columns, but shorten them. So I would use the first number "420", the number from "Nascar 2" and the first letter from "Honda". The solution would be 420-2-H and I need to make a formula for that with the "-" sign in between. Help please!

    1. Hey there:
      I would contemplate a formula using a number of different methods:

      Step 1 is simple and that would just be: =(A1) (for example)
      The second step requires extracting a number from the string and a good reference to achieve this can be found here: https://www.ablebits.com/office-addins-blog/excel-extract-number-from-string/
      The third and final step is simple: RIGHT(C1;1) (RIGHT(Cell-Ref;No's chars)

      Now that you have your formula sorted out, stitch them together using concatenate:
      =Step-1&"-"&Step-2&"-"&Step-3
      Replacing Step-1 through Step-3 with your formula. The = sign is used once at the beginning of the formula.

      Cheers... -M

  36. i want to extract name NEW SAHARA BEEJ BHANDAR-TIJARA from NEW SAHARA BEEJ BHANDAR-TIJARA-F C ABS (if sentence containing ABS) with the help of if condition.

    1. Vishal,

      Assuming your data is in A1, the following formula will help:
      =LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)

      1. Thanks.formula works.And i have one more problem.If two cell of consecutive rows contain names.One cell having e.g name NEW RAJ BEEJ BHANDAR-NASIK-F C ABS & other cell having name NEW RAJ BEEJ BHANDAR-NASIK.Here i want to extract name NEW RAJ BEEJ BHANDAR from first cell if it contain ABS and otherwise it will remains the same if it doesn't contain ABS

          1. My problem is suppose if two consecutive rows contain names e.g 1st cell does contain name NEW RAJ BEEJ BHANDAR-NASIK-F C ABS and in 2 nd cell doescontain name NEW RAJ BEEJ BHANDAR-NASIK.Now i want to extract only the name NEW RAJ BEEJ BHANDAR-NASIK from name NEW RAJ BEEJ BHANDAR-NASIK-F C ABS (when cell contains ABS). Otherwise the name must remains the same as NEW RAJ BEEJ BHANDAR-NASIK (when it doesn't contain ABS)

  37. i have a string in one cell with length of 400 characters ( A1) . Is there a way i can extract first 150 characters in ( B1) one cell, next 150 in (C1)2nd cell and balance in 3rd (D1)

    Thanks

    Sumesh

    1. Sumesh:
      The answer to your question is easy. My only question is why are there 400 characters in one cell?

      In B1 =LEFT(A1,150)
      In C1 =MID(A1,151,150)
      in D1 =RIGHT(A1,100)

  38. I get reports where the name format isn't consistent -- some are FirstName LastName and others are LastName, FirstName.

    Can I use an IF statement combined with your extract formulas to create a single formula that extracts LastName from either format?

    Like this: IF(entry contains comma, LEFT formula, RIGHT formula)

    Would that work?

    And thanks for the great examples and explanations!

    1. Hi Naomi,

      This idea has never occurred to me, but I suppose yes, it will work.

      Here's the formula to extract the last name:
      =IF(ISNUMBER(SEARCH(",",A1)), LEFT(A1, SEARCH(",", A1)-1), RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)))

      If you'd like to get the first name too, you can find the appropriate LEFT and RIGHT formulas in this tutorial: How to split names in Excel.

      1. I have this same problem. How would you handle the following variation
        John Doe, Jr.
        VS.
        Doe, Jr., John

  39. Hi, how do I remove a decimal point for a number 04.80 to be converted to 0480?
    Thanks for your help.

    1. Hi Emmylou,
      How are you doing, i think you can use substitute function as =SUBSTITUTE(YourCell,".","".
      Thank you.

  40. Hi,
    I have a sheet with a list of docs (>500k), with their respective path. I want to extract the folder name from the path. Since, it has folders and sub-folders, the folder name doesnt come at the same position. The path is reported as "E:\abc\defg\...\xyz\". Basically, I'm looking to extract the text between the last two "\" i.e. "xyz".
    Thanks in advance

    1. Sid:
      Is it always the case that the information you want is in between the last two forward slashes?

      1. I have the same question - yes the information I need is always between the last two “\”
        Can you suggest me a formula for this

        1. Hello!
          If I understand your task correctly, the following formula should work for you:

          =MID(A1,SEARCH("@",SUBSTITUTE(A1,"\","@",LEN(A1) - LEN(SUBSTITUTE(A1,"\",""))-1),1)+1, LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"\","@", LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1),1)-1)

          I hope my advice will help you solve your task.

  41. hi
    how can i change format 243F160918D1 to {24, 3f, 16, 09, 18, d1}?

    1. Julia:
      Not sure how to format a cell to display the number in the manner you want, but here's a formula to add a comma after every other character.
      Where the original text is in A83:
      =LEFT(A83,2)&","&MID(A83,3,2)&","&MID(A83,5,2)&","&MID(A83,7,2)&","&MID(A83,9,2)&","&MID(A83,11,2)
      As you can see we start at the left of the text in A83 return the first two characters and then concatenate a comma. Then we use the MID function to concatenate the text in A83 beginning with the third character and return the next two characters and concatenate a comma.
      Continue with this same process until all characters have been added to the new string which contains a comma after every other character except the last one.
      You can change the cell addresses, the number of characters to skip and/or return and the inserted character to suit your needs.

  42. SCPA-2F1ASO-PAC01-JB01-PA-100 How can i extract data between the 2nd and third hyphens to different cell and also the third and fourth unyo a differnt cell.

    Many thanks

    1. Bode:
      You can use the Text-to-Columns tool. It's under the Data tab. Just enter"-" as the delimiter.
      Or you can enter this into an empty cell and copy it across as many columns as needed. My test data was in A1, so you might want to change that address.
      You can also copy it down as many rows as needed.
      =TRIM(MID(SUBSTITUTE($A1,"-",REPT(" ",100)),(COLUMNS($A$1:A$1)-COLUMN($A$1))*100+1+SUMPRODUCT(LEN($A1:A1))-LEN($A1),100))

  43. I have a string (05/21/201806:59:0511.311.311.711.3) and wanting to break the data up. I know I can do Left(), Mid() and Right() to get what I want. But what I am trying to find a way to copy/paste without the formula in the final cell. I will need to be able to copy the separated data into another spreadsheet. Is there a way to paste into another cell without the formula in it?

    1. Matt:
      If I understand your question correctly I would say you can right click choose Paste Special, then choose the Value button. No formulas, only values.

  44. How to get first name from below name list

    aakash kumar, Arvind soni, Tejas Sawant

    I want to extract first name "Aaskash kumar"? How to do this? Kindly help me?

    1. =LEFT(D11, SEARCH(",",D11)-1)
      Cell D11 will have : aakash kumar, Arvind soni, Tejas Sawant

  45. Challenge: I need to extract a string between two other strings (a word and a character) within a cell, but those other strings may repeat inside the cell.

    For example - a scanning tool conducts 10 tests and returns a failure for one but includes all the Passes in the output. The test results are separated within each cell with *****

    multi-line cell example:

    Test: Endpoint
    Result: Pass
    Details: Endpoint Connected
    *****
    Test: Resolution
    Result: Fail
    Details: Unable to resolve FQDN
    *****
    Test: Port
    Result: Fail
    Details: Port 1234 does not respond

    First thought was re-organize the output using text to columns butfor whatever reason it doesn't work (even after replacing the ***** with a single char like ^ (I'm guessing it's thrown off by the carriage returns within the cell).

    So we're left with searching for some combination of Mid, Len, Search/Find but it's proving extremely difficult. It's easy to find the first instance of "Failed", but hard to then find the stopping point which has to be the very next ^ char after "Failed", while also then being able to repeat the extraction for any other Failed tests inside the cell.

    1. Vic, do you still need a solution for this? Also curious to know what tool you use for getting the port data and how you get it into excel. Some functions/searches can be done as part of the import procedure that are much harder to do afterward. cheers

  46. The search function you describe does not work at all.

    1. Hello,

      All the formulas discussed in this tutorial are available for download in this sample workbook, so you can make sure they all work as described.

  47. "Prescriber: Mitchell CNM H Becky

    Patient: Patient Name

    Medication: FE TABS 325 (65 FE) MG ORAL TABLET DELAYED RELEASE; Qty: 60 Refills: PRN

    Ordered: 29-Dec-2017@1130

    Signed/Transmitted: 29-Dec-17@1130

    Pharmacy: Access Family Care Pharmacy*
    530 South Maiden Lane
    Joplin, MO
    Ph: 4177827209
    Fax: 4177827727"

    Above is a text field in an excel spreadsheet. I need to make the medication in a field by itself.

    Can you tell me how to pull what is after Medication:?

    1. I'm sure there may be a more efficient way but you can do the following:
      =MID(A1, SEARCH("Medication: ", A1) + 12,SEARCH(";", A1, SEARCH("Medication: ", A1)) - (SEARCH("Medication: ", A1)+12))
      Assuming your text is in A1.

    2. FE TABS 325 (65 FE) MG ORAL TABLET DELAYED RELEASE; Qty: 60 Refills: PRN
      I will assume all the strings are in the format "Medication; Qty"
      1) To extract the medication, or what's before the semicolon, use:
      =LEFT(String, SEARCH(";",String)-1)
      2) To extract the Qty (and what's after), use:
      =RIGHT(String,LEN(String)-SEARCH(";",String))

  48. Nice primer, however, extracting a number from a string leaves me with another string. When I convert these strings to numbers via format I can't operate on them as numbers, they are still strings?

    1. Hi Steven,

      Right, whenever you use a Text function (Right, Left, Mid) to extract something, the output is always text. In case of extracting a number from a string, the result is a numeric substring, which in terms of Excel is also text, not number. To convert it to number, you can wrap your formula in the VALUE function. Here's an example in the simplest form:

      =VALUE(LEFT(A2,4))

      And thank you for a smart question! I've added this tip to the article.

      1. Or else you can multiply it by 1, with iferror.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)