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 9. Total comments: 445

  1. Hi there:
    I am struggling to understand something here. I have the following string "avro_67563908_289(000).csv" to which I apply the following formula: `=MID(A6;SEARCH("_";A6)+1;SEARCH("_";A6;SEARCH("(";A6)+1)-SEARCH("_";A6)-1), which results in a `#VALUE!` error. I have spent quite a bit of time trying to understand this, given that the following test formula works: `=MID(A6;SEARCH("_";A6)+1;SEARCH("(";A6)-SEARCH("_";A6)-1)` - it just starts from the first `_` and not the second which is what I want.
    Is there anything obvious that I am doing wrong... the field format is `General`.

    Thanks.
    -M

  2. 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

  3. I want to extract 200 from "200 Euro", "Euro 200", "Euro-200", "200 Euro - 500 Euro",
    all are in a column.

  4. Everything I read from Svetlana Cheusheva is always clear, straight forward and fill with examples. Damn I wish I knew more about MS Excel like her :o)

    Thank you for your excellent postings

  5. Hello!
    Could you please help me to create a formula to obtain the information of the second and the third bracket:

    [Ignacio] Status changed from [one] to [Tweleve]
    [Carlos] Status changed from [hundred] to [five]

    Thanks for you help!

  6. Dear
    Thanks For your Gide to separate W1 from {W1_21} that is sheet name of EXCEL generate from command of {MID(CELL("filename",A29),FIND("]",CELL("filename",A29))+1,255}

  7. how can we pull numbers from any alphanumeric string using a formula.
    Example-
    asd123sd
    123bhs23sd
    sde234jh213

  8. Hi!
    I need to extract following words in my cells, which dont have any specific character and at the same time the amount of 1st words are not same:
    LOOMBNB
    BNBUSDT
    I should separate BNB from a 1st line and USDT from the 2nd one. as you see, I cannot use Text-To-Columns as well.
    please help!
    Thanks

  9. Hello! I have the below and depending on the length of my I need to pull everything up until the second hyphen so:
    PRO85-2700-270S would become PRO85-2700
    SPRI87-4000-400S would become SPRI87-4000

    but then I have some strings with 3 hyphens and I only need to pull up to the 3 characters after the 2nd hyphen.
    Example:
    SLAE01-7000-330-7009-11 would become SLAE01-7000-330

    I have a file with 7K+ rows so any helps would be AMAZING and greatly appreciated!!

    Thanks!
    Nancy

  10. I am using the formula: =SUMIF(Laurie!$A:A,B2,Laurie!$E:$E) to try and get names of people from a separate worksheet tab (tab is named Laurie). But the cells in column E on the Laurie tab are not numbers but someones name. So basically if I put a number in column E cells on the Laurie tab it will show up correctly in my current worksheet tab.
    But any names or non-numbers in the column E cells show up as 0 in my main worksheet. I am guessing this is because I am using the SUM function so it is trying to sum up any number in each cell in column E.
    Anyone help for which function would be best to get the name (or words) form those cells in that column would be great.
    Thanks.

  11. The text string is "47 Nelson Street Kettering Northamptonshire NN16 8QN" in Cell A2.
    Here I need the street(47 Nelson Street), Town(Kettering), county(Northamptonshire) and Code(NN16 8QN) in each separate cell of a row.
    How to do this? Give me the formula for each retrieval.
    Also, another string is "Ref. No: 19/00443/EPHMO Status: Current Licence Applicant Name: Mr Jack Oliver McLoughlin" in cell A3.
    I need only the ref no (19/00443/EPHMO) and the name (Mr Jack Oliver McLoughlin) in separate cells.
    Help me with these formulas.

  12. Sir/madam,
    If a cell contains variable numbers (0 to 6) if cell value is 1 instead of 1 it should display Sunday and so on. Please give me the solution in Microsoft excel.
    Thanks.

  13. I need to extract the first 3 numbers, between the 2nd and 3rd dash. The number of characters between the dash can vary between 3 and 4.
    I purchased the suite and have also used the above formulas and can’t get it to work. It always extracts the complete string between the “-“. I must have it extract only the 1st 3 characters between the dashes.

  14. Hi I'm Rahul Actually I'm new Learner Excel Can You Help Me in Excel

  15. I am trying to extract all letter characters (to the right) following the numbers in the middle of each text line. For example, the first line, I would only need "BLUE CROSS BLU"

    ACH DEPOSIT BCBS INC 462275727 101000011824415 BLUE CROSS BLU
    ACH DEPOSIT HCCLAIMPMT 1932440070 031100200512323 AETNA A04
    ACH DEPOSIT HCCLAIMPMT 1932440070 031100204517462 WPS

    Any ideas on how I could do this?
    Thanks!

  16. i need help to identify in single cell with colour as we have three different things
    5 Pkt -Tata Rallis Fungicide Captaf (Size=500 gm)- 309 -
    1 Unit PGR Induce G 500 ml Rs. 515
    1 Unit Humic Acid Roota 1 kg Rs. 850"

  17. 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)

  18. I am trying extract only the GB information from a cell in Excel. However in my report, there is an unknown number of times that the GB information will appear and I have at least 2000 lines to go through.

    A cell could look like this:
    0 GB (0% or 1.0:1), 9 GB (31% or 1.5:1)
    or like this:
    453 GB (29% or 1.4:1), 728.25 GB (33% or 1.5:1), 422.25 GB (33% or 1.5:1), 923.5 GB (42% or 1.7:1), 705.5 GB (33% or 1.5:1)

    I want to extract each occurrence of the GB amount (i.e. 0 GB, 9 GB from the first example above and 453 GB, 728.25 GB, 422.25 GB, 923.5 GB, 705.25 GB from the 2nd example above)

    The ultimate goal is to be able to add the GB amounts together for each cell and get a total. I tried splitting the text, but because some of the cells have so many occurrences of GB's it didn't seem like a good choice.

    Does anyone have any ideas?
    Thank you in advance,
    Joanne

  19. Hello i'm trying to extract dimension from an item name column

    E.g. item names are all in a column and one would be "White fence post 80x80 1.8M" but another might be "RT1 35x150 treated decking 4.8MT"

    In these cases how do i pull out just those numbers?

  20. I want to extract last 2 word before and after "-" in one formula
    eg.
    Abc-ccc-ddd, here I need ccc-ddd

    Fff-hhh-kkk-yyy, here I need kkk-yyy

    Kkkk-hhh, here I need kkkk-hhh

  21. If I have this string:

    Babababa{njnj}v_ANDREWGORT@andrew@gort.com

    How do I extract the 'ANDREW' alone?

    This is what I have:

    =MID(C2,SEARCH("v_",C2)+2,SEARCH("GORT",C2, SEARCH("v_",C2)+2) - SEARCH("GORT",C2)-1)

    It appears that "GORT" doesn't work. Is there any way to do this?

  22. I have a data format like this "QA_CASE_1234_John_Smith" How do I extract John Smith in a separate cell without any underscore in John Smith name.

  23. Hi,

    Need some help with extracting data from cell with multiple lines. For example below data is in a single cell. This is a report generated against multiple software with found version and existing version. There are thousands of cell with similar data with "Found: xxxxx". I would like to know a way to get ONLY "Found: xxxxx" data into another column. Would that be possible?

    --------------------------------
    Tested: C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.dll

    Found: 4.7.3130.0

    Context:

    Fix: Install the appropriate patch from Microsoft or through Windows Update.
    --------------------------------

  24. 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)

  25. Many thanks! The last name extract worked beautifully -- I'll try the first name next.

    Doug also sent me information -- I really appreciate everyone's help.

  26. 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

  27. 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.

  28. Abdul:
    If the data is in H37 as one long string, enter this in an empty cell.
    =MID(H37,SEARCH("CHQ",H37)+4,8)
    It says, in H37 find the first "CHQ" go forward 4 characters which includes the "CHQ" and a space and return the next 8 characters.

  29. how can i seperate out cheque no in a seperate cell plz quide me. the data in cel is:
    Cell data
    Cash Withdrawal # 58989,CHQ
    00060706,AHSAN JADOON
    4240113035499 CHQ 00060706 DHA SHAHBAZ BRANCH,4240113035499

  30. 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.

  31. 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.

  32. 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))

  33. Regarding my previous post - I'm trying Text to Columns - awkward, but I think I got it.

  34. Thanks - I tested out the functions and learned something new. I can extract the JPN or GB from "Chatoyancy(JPN)" or Morgan's Mambo(GB) - BUT I really need to separate the text so I am left with "Chatoyancy" and "JPN", or "Morgan's Mambo" and "GB".

  35. 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.

  36. Hi,
    wanting to understand how to implement Case statement in Excel 2010. I have more than 20 conditions to match and if else is not helping me out. also I have researched in the internet they have asked me to make use of Choose function witch do not work either. could you please help how I can overcome the problem. here is some sample code that I have come up with to resolve my issue.
    =CHOOSE(
    (
    ( 2 * COUNT(FIND("ID",AR2))>0) +
    ( 4 * COUNT(FIND("CD",AR2))>0) +
    ( 8 * COUNT(FIND("DTE",AR2))>0) +
    ( 16 * COUNT(FIND("PH",AR2))>0) +
    ( 32 * COUNT(FIND("AMT",AR2))>0) +
    ( 64 * COUNT(FIND("PER",AR2))>0) +
    ( 128 * COUNT(FIND("TOT",AR2))>0) +
    ( 256 * COUNT(FIND("NME",AR2))>0) +
    ( 512 * COUNT(FIND("NUM ",AR2))>0) +
    ( 1024 * COUNT(FIND("DESC",AR2))>0) +
    ( 2048 * COUNT(FIND("TYPE",AR2))>0) +
    ( 4096 * COUNT(FIND("ADDR",AR2))>0) +
    ( 8192 * COUNT(FIND("EMAIL",AR2))>0) +
    ( 16384 * COUNT(FIND("RATE",AR2))>0) +
    ( 32768 * COUNT(FIND("PCT",AR2))>0) +
    ( 65536 * COUNT(FIND("NOTE",AR2))>0) +
    ( 131072 * COUNT(FIND("ZIP",AR2))>0) +
    ( 262144 * COUNT(FIND("STE",AR2))>0) +
    ( 524288 * COUNT(FIND("TITLE",AR2))>0) +
    ( 1048576 * COUNT(FIND("SUM",AR2))>0)
    ),
    "I_",
    "C_",
    "D_",
    "T_",
    "A_",
    "P_",
    "A_",
    "T_",
    "I_",
    "T_",
    "C_",
    "T_",
    "T_",
    "P_",
    "P_",
    "T_",
    "T_",
    "T_",
    "T_",
    "Q_"
    )

  37. 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

  38. 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

  39. 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.

  40. "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))

  41. When I download weekly the raw date to an excel spreadsheet I was looking for a way to extract the name from the name-and-initials/initials-and-number from the raw data. Your tip (Left(cell, Search("char",cell)-1) is excellent. Below are some fictitious names:
    Hofman, Todd (th81)
    Medemblik, Terry (tjm)
    Williams, Mark (mrw)
    Vanderzwaag, Ron (rvz)
    Linde, Tim (tl1074)
    Nijenhuis, Andy (andy)
    Verhey, Tim (tmv)
    Moesker, James (jm1181)
    Martin, Nevin (nwm)
    Swaving, Brad (bs776)

  42. 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



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)