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

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

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

  3. This is very helpful, thank you!

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

  5. How can I extract "TG" from the following:

    EVERGREEN: HNTB Holdings ltd - TG Only 1/1/2022

    1. Hi!
      I’m sorry but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, but I believe the formula below will help:

      =IF(ISNUMBER(SEARCH("TG",A1,1)),"TG","")

  6. hi! I have a data written in (Name | DGL | Capacity | DISCO) i have to extract the DISCO from the cell but my All excel sheet and data is not in this format some are of other format Can you please tell the formula from which i can extract DISCOs where they are written and left the other cells blank where the discos are not written

  7. Thank you,,,thank you,,,,thank you for your tips. By reading all pertinent examples (LEFT, RIGHT, MID and so on) on how to extract (or seek the presence of a substring in a cell ) was able to get rid of unwanted old data in my worksheet using the SEARCH function. The SEARCH function gave me a TRUE or FALSE value according to the match and then I sorted and got rid of all TRUE conditions. Kudos to You. You are the expert.

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

  9. Dear All,

    please any one can help me to post some basic excel formulas.

    Thanks a lot

  10. Hello,
    I am working on simplifying my excel spreadsheet for work and I cannot seem to come up with the right function.
    My spreadsheet looks like a list of employees. I need to be able to take the first letter of the first name, the fourth letter of the first name then the first letter of the last name and the fourth letter of the last name and put it into another cell.
    Ex. if the name is Robert Michaelson, I need 'REMH' to be extracted from it. Does anyone know how to do this?
    Thanks!

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

  12. I am not able to registration number from the sentence. I.e. AA01WW1111 TO ZZ11WW1111. Every time I am using mid and find formula with changing first two letters to pick registration numbers. Is there any alternate?

    1. Hi,
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? What formula are you using?

      1. Hi,

        I am not able to get the 10 digit registration numbers from a sentence. Its contains various state registration numbers in each cell. At present, I am using the below formula.

        =mid(find,a1("AA",A1,1),10) to get AA10DE1234 from the sentence.

        1. Hello!
          If your text is AA01WW1111 TO ZZ11WW1111, then you can extract both codes using the formula

          =MID(A1,FIND({"AA","ZZ"},A1,1),10)

          Hope this is what you need.

          1. Hi

            Thanks for your support. I wish to bring your notice that i want to find the codes between AA01AA0000 TO ZZ99ZZ0000. In this connection I have to fetch the first two characters which contains from AA to ZZ in a cell.

            I furnish below the table for your references.

            TEXT IN THE SENTENCE USED FORMULA RESULT
            AA01DD1234 =MID(B3,FIND("AA",B3,1),10) AA01DD1234
            AB01DD1234 =MID(B4,FIND("AA",B4,1),10) #VALUE!
            AC01DD1234 =MID(B5,FIND("AA",B5,1),10) #VALUE!
            BC01DD1234 =MID(B6,FIND("AA",B6,1),10) #VALUE!
            DC03BD3214 =MID(B7,FIND("AA",B7,1),10) #VALUE!

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

              =CONCAT(IFERROR(MID(A1,FIND(K1:K20,A1,1),10),""))

              K1:K20 - range with the desired values - AA, ZZ, etc. There should be no empty cells.
              If there is anything else I can help you with, please let me know.

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

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

  15. 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. Hello!
      Sorry, it's not quite clear what you are trying to achieve.
      Specify what you want to do. What result do you want to get? R and E is 4 and 6 characters. To extract 3 and 5 characters, you can use the MID function

      =MID(E1,3,1)&MID(E1,5,1)

    2. Just correction
      3rd & 5th are LH

  16. 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!

  17. ASCN12-1Z-CD8,BHFT

    Hello All,

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

    Thanks

  18. Hello!! Please help:
    how can i make a formula that: from a text in a cell TO CHOOSE the 3rd to 5th string AND at the same time the same product NOT TO BE repeated in the column AND IF repeated then CHOOSE THE NEXT consonant (not a vowel) AND IF reapeted (given the above formula) THEN SUBSTITUDE with numbers in row 1,2,3 etc?? ?

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  19. Halo!
    I am this string of text and would like to extract out the consultation remarks and when I tried using the MID function, I am unable to get the remarks. Possible to help? Thanks
    RegistrationId : NYHAE20200820, ConsultationRemark : Testing right side of LHR,CreatedBy : Scott_Reynolds, CreatedDate: 22/8/2020 12:59:31 PM,UpdatedBy : Scott_Reynolds, UpdatedDate: 22/8/2020 12:59:31 PM

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Thank you!

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

  21. Extremely useful website! Thanks for sharing.

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

  23. Hello,

    I have a column in column A that has movie names and release dates as one text string. How do separate the movie name from the long date? I've figured out how to extract when it's only the movie name and date together, but sometimes the movie synopsis follows the date in the same column. This is where it stumps me. To split the movie and the date, when it's only those two items, I have a column for each month, and have used =iferror(left(a2,find($f$1,a2)-1),"") where f1 is the heading for January, g2 for February, h3 for March and so on through December. This has successfully given me the movie name, but in the column i have for the date, I have this formula (=text, e2,"mmmm dd, yyyy"). When the synopsis follows the date, I'm left with the date followed by a paragraph of text in that column that I can't seem to get rid of.

    Thank you,

    Mike

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      Please provide me with an example of the source data and the expected result.
      It’ll help me understand your request better and find a solution for you.

      1. The formatting of my reply should look nothing like that. I want three columns. The first column is for example: The King's ManSeptember 18, 2020 As a collection of history's worst tyrants and criminals....

        The second column, I want to output just the movie name

        The third column I want to output only the date in long format. I don't want the text that follows the date in the source column kept at all. I want to strip that and toss it away.

      2. is there a way to upload a snippet of my worksheet so you can see it? In essence I have one column that I want to split the date from the text. The source column would be formatted like:
        Source Result Result
        Column A Column B Column C
        Movie nameJuly 22, 2020 Extraneous text Movie name July 22, 2020
        Movie nameAugust 8,2020 Movie name August 8, 2020
        Movie nameSeptember 4, 2020 Alphanumeric Movie name September 4, 2020
        Movie nameMay 1, 2021 Text text text text Movie name May 1, 2021

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

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

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

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

  28. LEFT(cell, SEARCH("char", cell)-1) is wrong, its ; instead of ,

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Thank you!

  29. I just have to say Thank you so much for this article!

  30. I want to extract the contents of all cells containing a certain substring delimited by a "," or ";".
    For example:
    Cell A1 contains "0", Cell B1 contains "index:1", Cell C1 contains "0", Cell D1 contains "index:2"
    I want to find all the cells that contain the word "index" and when found, to extract the full contents of the cell into one cell. In the above example, I am looking for a return value index:1,index:2.

    How can I do that?

  31. I'm trying to enter the date in one cell (01-Jan-2020) column A and want excel to split it into three columns B,C,D (day, month and year). could you please help me in formula

  32. Dear can somebody help me to extract Q amount from below string,
    LON AC YTO Q125 Q100 AC YMQ Q125 AC YYZ

    Thanks

  33. Thank you!

  34. i want to extract only LHS or RHS from the text, how to do it
    i.e 1234 LHS WERT SERT
    1234 RHS WERT SDFR

  35. How do I cut the specific content from a cell?

  36. Hi, I have long texts in the excel cells and I would always need to extract only the Impacted country which differs from cell to cell (in the example below it would be India). Only the text before the country is constant "Impacted Country (For multiple countries select global) = "

    Can anybody help how can I do that?

    Text in 1 excel cell is for example as below:
    ---------------------- USER ENTERED DATA ----------------------
    Operating System = Windows 10
    Impacted Country (For multiple countries select global) = India
    Idoc Number: = (NOT ENTERED)
    GSAP Transaction Code: = (NOT ENTERED)
    EMCS Table = (NOT ENTERED)
    Document Number: e.g. BOL, Nom key, LRN# = (NOT ENTERED)
    EMCS Dashboard Status = (NOT ENTERED)
    Interface ID: = (NOT ENTERED)
    Load date/Time = (NOT ENTERED)

    Thank you for your help in advance!

  37. How to extract the texts between 3rd and 4th or in case (last - 1) texts between instances of a character

  38. I have a long text (which is a extraction of narration from accounting entries) which contains month and year like "salaries for the month of FEB 2018", "cheque number 123456 paid to Mr. Rajan towards salary for the month of Feb 2018". Now i want to extract the month and year alone. Can somebody help me.

  39. Hi, what should be the formula if I want to extract text from the cell but the result doesn't match the character that I am looking for? the best scenario is as below:
    14933 - Cologne West
    15116 - Tador East
    12345 - Cologne East
    45678 - Tador South
    36789 - Sucat
    15674 - Alabang

    From the list, I want to extract only the text but with a condition that if it is not "Tador", just copy the location, otherwise just put "Tador". I have tried several formulas but to no avail.

    Hope you can help me out on this, many thanks in advance!

  40. Hello,
    I need to know how to extract everything to the left of a string in a list where there is not the same number of characters on the left side or right side of the string in a column. For example, I need to extract everything to the left of " - Garage 1" and " - Store 2" and " - Building" So, I want to get: Supplies-Other, Repairs-Equip, Salaries-Staff. I'm sure this is pretty simple, but I can't figure it out. Thanks in advance for any help anyone can provide!

    Supplies-Other - Garage 1
    Repairs-Equip - Store 2
    Salaries-Staff - Building 3

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

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

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

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

  45. 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!

  46. 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}

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

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

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

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

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 :)