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

  1. Exelent

  2. hi ...
    I need to extract the check sign from the name and count the sign after the extraction
    would you please help me with that .....

    1. R tafis ✅✅✅✅✅✅✅✅
    2. Anton ✅✅✅✅✅✅✅✅✅✅
    3. Iqbal ✅✅✅✅✅✅✅✅
    4. Kosong
    5. Irvan ✅✅✅✅✅✅✅✅✅
    6. Rommy ✅✅✅✅✅✅✅✅✅✅✅✅
    7. Eva Nudin ✅✅✅✅✅✅✅
    8. Yun Haryadi ✅✅✅✅✅✅✅✅✅
    9. Guna ✅✅✅✅✅✅✅✅✅
    10. Gun S ✅✅✅✅✅✅✅✅✅
    11. Gun S ✅✅✅✅✅✅✅✅✅
    12. Muklis ✅✅✅✅✅✅✅✅✅✅
    13. M.Nasir ✅✅✅✅✅✅✅✅✅
    14 Fahmi ✅✅✅✅✅✅✅✅

      1. hi ....
        thanks for your kind reply ....
        i'm not intend to remove that character, i need that character to be separated in one column ...
        how should do that so that i'm gonna have a column which filled with the characters alone ....
        thank for your kin attention

        regard

        1. Please re-read the article above, it covers your case completely. Use the SEARCH function to determine the position of the desired character. Use the MID function to extract characters from the text starting from this position.

          =MID(A1,SEARCH("✅",A1),20)

          1. great .... i found it .....
            thanks a lot ....
            really helpful

  3. I want to extract one word from the following.
    Can you help me with this

    "Å KODA KUSHAQ 1.0L TSI 85KW AT AMBITION BSVI-PH2"
    I want to extract only "KUSHAQ" from the above sentence in excel. Please suggest formulae

  4. Hi all, need help with this one please. I have this in a cell "Treasury rate: 4.85%Tenure: 12 month(s)", and i need to extract these in to separate cells in the same row:

    Cell1: 4.85%
    Cell2: 12
    Cell3: month(s)

    Appreciate some help

  5. I have this in a column and I need to extract the word in front of ISS: Like "Anifowose ISS" and "Sanya ISS". What will be the line of formular pls?

    1. Refill of 33kV Bus Section SF6 gas at Anifowose ISS
    2. Maintenance of 33kV yellow phase transformer breaker at Sanya ISS that was reported glowing.

    Thanks

      1. Hi, and thank you for all the answers you've posted!!
        Can you also use the TEXTBEFORE AND TEXTAFTER functions in an IF formula?

  6. Please help.
    BMW - Active E 094
    how to extract Active E from the text?

    1. Hi! You can use these formulas for this text string:

      =MID(LEFT(A1,SEARCH("#", SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))), SEARCH("-",LEFT(A1,SEARCH("#", SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))+1,20)
      or
      =TEXTBEFORE(TEXTAFTER(A1,"-")," ",-1)

      Here is the article that may be helpful to you: Excel TEXTBEFORE function - extract text before character (delimiter) and Excel TEXTAFTER function: extract text after character or word.

  7. Hi there,

    im trying to create a string that would provide me with the right info here that i can use for a vlookup from another table.

    I have some that I need the data before the dash-, some without the dash at all where i need one or two characters and some where i need the first character after the dash as well. so for instance:

    8Q-123 - I need 8Q-
    N12645 - I Need N
    VP-C234 - i Need VP-C

    is this possible?

    thanks

    1. Hi! There is no common pattern or regularity in your desired results. Use a separate formula for each of your examples. All the information you need is in the article above. For example,

      =LEFT(A1,3)

      1. Unfortunately that wont work as the data is changed frequently.

        thanks anyway!

        1. I have string come out from the machine that I need to extract. Format as following : 20hour23minute34.56 or 1hour5minute14.16, how can I substract minute value from the text.

  8. Hi,

    I need a help if anybody can help. I just need to extract the letter between the numbers.

    Data in column A:
    362T200
    1000RT200
    1000MST200

    What I need to get in column B:
    T
    RT
    MST

    Thank you for any help!

  9. Hola, necesitaría vuestra ayuda si es posible...

    De la siguiente cadena "ALFARO ALMEIDA, ALFREDO", me gustaría extraer a otra celda todo lo que está antes de la coma, de forma que pueda separar los apellidos del nombre, ya que los recibo de esa forma.

    He probado la siguiente fórmula que he visto en tutoriales, pero me da error: =IZQUIERDA(F2,BUSCAR(",",F2)-1)

    Gracias de antemano, saludos.

  10. How to extract a word from Cell A to Cell B based on the list/range given in Cell C.
    For Example-
    Given - Cell A - I like Dogs
    I need the word "Like" in Cell B
    Based on Cell C range - Like , Love , live etc.
    What would be the formula?

      1. Hi!
        I passed 1day searching to do the same thing but with wildcards and possible multiple matches across a same string.

        "sample sentences" "Expected_Results" "Lookedup_Patterns"
        wrong spell match : A.01 sample A.01 A.?
        single match A2.22 sample in sentence A2.22 A?.
        double match test B1.44 and B2.55 sample B1.44, B2.55 B?.

        I tested many combinations but none works the way I need ! This is the closest I achieved :
        {=TEXTJOIN(",";TRUE;IFNA(INDEX(TEXTSPLIT($M2;" ");MATCH($S$2:$S$4&"*";TEXTSPLIT($M2;" ");0));""))}

        what look strange is that the return of the MATCH() is a list of 3 items and thus don't list more than one similar pattern match per sentence

        Thanks in advance for your expertized advice ;)

        1. Sorry, it's not quite clear what you are trying to achieve. To ensure clear understanding of your task, can you provide an example of the source data and the desired result you are aiming for?

          1. Ok sorry for that,

            Column M : include 3 rows with sample sentences :
            row2 = sentence 1 = "wrong spell match : A.01 sample"
            row3 = sentence 2 = "single match : A2.22N sample in sentence"
            row4 = sentence 3 = "double match : with B1.44 and B2.55 sample"

            Expected results are
            row2 = A.01
            row3 = A2.22N
            row4 = B1.44, B2.55

            Column S was my list of patterns to be tested by the MATCH() command

            is it more clear ?

            any other patterns like "-22.55" shall not be returned

        2. sorry, the tabs were not preserved :
          Column M - Column Q - Column S
          "sample sentences" - "Expected_Results" - "Lookedup_Patterns"
          wrong spell match : A.01 sample - A.01 - A.?
          single match A2.22 sample in sentence - A2.22 - A?.
          double match test B1.44 and B2.55 sample - B1.44, B2.55 - B?.

          1. Forgot to say that I got it to work using Alex's VBA RegExpExtract code and the "\s[A-za-z0-9]+\.[A-za-z0-9]+\s" pattern...
            but would have loved to find a non-VBA dependent formula.

  11. HI! i need a super big help. I'd love to have your knowledge and expertise to find a solution to this:

    i have two columns with information (text) that is not coming in the same order EX:

    Column A: Finance Service Account; HR Business Partner; Client Account
    Column B: Client Account; Finance Service Account

    I am ok with the ones which are repeated, i want a formula to find the differences between the two columns and throw the HR Business Partner as a result. your help would be very much appreciated. thank you!

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

      =IF(CONCAT(CHOOSECOLS(TEXTSPLIT(A1,";"),3,1)) = CONCAT(TEXTSPLIT(B1,";")), CHOOSECOLS(TEXTSPLIT(A1,";"),2),"")

      Use the TEXTSPLIT function to split the text by separators. Use the CHOOSECOLS function to get the desired parts of the text. Combine these parts with the CONCAT function and compare them.

  12. please help with my requirement , i need to extract all string of 10 characters from a column ,

    the format of the string is XX12345678 ( first two characters are alphabets and next 8 will be numbers)

    is it possible ? can someone help with the formula.

    1. Hi! If you want to extract the first two characters, use the LEFT function as recommended in the article above.

      =LEFT(A1,2)

      1. Thank for the care and response.Apologies if i was not clear.

        The position of the string is not reliable. the query is to extract all 10 letter string from a cell.

        the position can be anywhere,

        for EG:

        please process below components

        xxxxxxxxxx
        yyyyyyyyyy
        aaaaaaaaa

        above is the information in a cell, which may vary and does not have a standard format,

        but the components (xxxxxxxx or yyyyyyyyyy or aaaaaaaaaa)will be 10 letter string.

        here when i run the formula i need to extract all the 10 letter components alone.

  13. [{"wid"=>"XI62KKQ", "product_title"=>"PHILIPS HD6975/00(882697500010 Grey 8710103895244 Grey 8995", "quantity"=>1, "fsn"=>"OTNFGZ6VGX4AHYRH", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B7-G0-006", "product_type"=>"otg_new", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"XIA5758", "product_title"=>"Pigeon, Gas Stove Combo - Brunet 3 Burner Gas Cooktop + Flat tawa 250 + Fry Pan 240, 14776, Black, 8904216517760, Stainless Steel, Manual, 3, Brass Burner, 2 Year Warranty from Manufacturer 5494", "quantity"=>1, "fsn"=>"GSTG4ACRZKMZTMH2", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B9-G0-012", "product_type"=>"gas_stove", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"XIB7OIF", "product_title"=>"Butterfly Rapid Plus Black 8906134070730 3399", "quantity"=>1, "fsn"=>"ICTG8MY8VZKZWYDY", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B9-G0-012", "product_type"=>"induction_cook_top", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"VN82165", "product_title"=>"EUREKA FORBES Trendy Zip Red & Black 8901561211282 Red, Black 3799", "quantity"=>1, "fsn"=>"VCLE8YYKSCFXJHE6", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-C1-G0-015", "product_type"=>"vacuum_cleaner", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["S5578085164"], "serial_numbers"=>[]}]}, {"wid"=>"XI5WROX", "product_title"=>"Inalsa QuickVac Red, Black 8903019008123 Red, Black 4195", "quantity"=>1, "fsn"=>"VCLFJ227GEGB2TR2", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B9-G0-017", "product_type"=>"vacuum_cleaner", "relabel"=>false, "item_details"=>[{"order_item_id"=>"@", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}, {"wid"=>"XI7NUV7", "product_title"=>"HAVELLS Instanio White & Mustard 8901762420209 12260", "quantity"=>1, "fsn"=>"WGYFT4CAEQH3QZPD", "sku"=>"SKU0000000000000", "package_id"=>nil, "location"=>"R1-B7-G0-006", "product_type"=>"water_geyser", "relabel"=>false, "item_details"=>[{"order_item_id"=>"ANRCA03E5672A7B4B69B9CB9DFE9B66D507", "status"=>"picked", "quantity"=>"", "shipment_ids"=>["@"], "serial_numbers"=>[]}]}]

    In this string I need to retain value "wid"=>"#####", "wid"=>"#####" as many times it occurs.

    Please help me with the formula so that I can extract values .

    1. Hi! If I understand your task correctly, the following tutorial should help: How to extract substrings in Excel using regular expressions (Regex).
      Try this formula:

      ="""" &RegExpExtract(A1, "(wid)(.*?)(,)")

      I recommend also paying attention to the Regex Tools. With this tools, you can find, extract, remove, or replace strings that match a regex pattern you enter. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  14. Hello Alexander Trifuntov (Ablebits Team)

    Please help here to get excel formula to calculate OT (overtime hours count),

    Example as follows :

    In time -9:00:00 AM
    Out time-7:20:00 PM
    Working Hours-10:20
    OT hours - ?

    Below is the output details but we need formula for the same,

    Working Hours Completed OT (Overtime)
    9:00 hours 0
    9:44 Hours 0
    9:45 Hours 1
    10:44 Hours 1
    10:45 Hours 2
    11:44 Hours 2
    11:45 Hours 3

    Criteria to apply
    1.Minimum working hours should be 9 hours
    2.for first OT(Overtime) tobe count for 45 min's after that it takes 1 hours

    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:

      =INT(D1/TIME(0,45,0))

      D1 - working hours

      1. Sir

        Minimum working hours should be 9 hours
        for first OT(Overtime) tobe count for 45 min's after that it takes 1 hours

        In time -9:00:00 AM
        Out time-7:20:00 PM
        Working Hours-10:20
        OT hours - ?

        We need to calculate OT (Overtime),
        below is the example for OT Calculation -Manual working

        Ex1- If 9:00 hours completed, OT Will be 0
        Ex2- If 9:44 Hours completed, OT Will be 0
        Ex3- If 9:45 Hours completed, OT Will be 1
        Ex4- If 10:44 Hours completed, OT Will be 1
        Ex5- If 10:45 Hours completed, OT Will be 2

  15. Hello,

    thank you for a great article!

    I have a situation as follows: a textstring in cell A1, which was the caption of a post on social media. This caption contains (besides text) multiple links/URL. In cell B1, I would like to have a list of those links/URL only.

    This is a repetitive task, in which I have a list of post text in column A. Each cell shall consist different number of links/URL.

    Which formular a could use to obtain such a list in column B? Many thanks.

    Regards
    ED

  16. Hi!

    I have a cell with multiplication formula. for example c2 is "=3,2*2,6". I want to take those 3,2 to d2, and 2,6 to e2.

    Sorry for my english!

    1. Hi!
      If your formula is written as text, use substring functions to extract numbers from text.

      =--LEFT(C2,SEARCH("~*",C2)-1)
      =--MID(C2,SEARCH("~*",C2)+1,10)

      If a formula is written in cell C2, use FORMULATEXT to get formula text.
      =--LEFT(FORMULATEXT(C2),SEARCH("~*",FORMULATEXT(C2))-1)

      I hope my advice will help you solve your task.

  17. Hello!

    I am processing product information for an online shop and I need to make a meta description for Google. There is a limit of 160 characters.

    From the beginning of the product description, I should take no more than 160 characters. In order for the sentence not to appear incomplete, is it possible to make a logic or a formula with which we can take text ending with the symbol " ." /end of the sentence/, but the total length is not more than 160 characters. It can be less than 160 but not more.

    For an example of the text below, I need to take up to 160 characters, but finish to the end of a sentence:

    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective. It can also be displayed on an external screen, not only on a PC but also on a TV. The microscope is compatible with Windows and Mac OS devices, and you can connect the microscope to them using a USB cable.

    160 characters arе:
    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective. It can also be display

    But I want to get the characters till the end of the first sentence:
    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective.

    or till the end of the second sentence:
    The Discovery Artisan 128 digital microscope has a built-in 3.5-inch LCD display, onto which the image is transferred from the objective. It can also be displayed on an external screen, not only on a PC but also on a TV.

    Please note that every product description is different and I`m looking for an automated formula or rule to take the information of many products into one Excel file.

    Thanks in advance!

    1. Hello!
      Use the MID function to extract 160 characters from the text. Use the XMATCH function to find the position of the last dot in these characters. Extract this number of characters.

      =LEFT(A2,XMATCH(".",MID(A2,ROW(A1:A161),1),0,-1))

      I hope it’ll be helpful.

  18. Need to find the count of Agents under TL2 with a score >80

    TL Score
    TL1_Agent1 98%
    TL2_Agent1 52%
    TL2_Agent2 88%
    TL3_Agent1 40%
    TL3_Agent2 77%
    TL3_Agent3 101%
    TL3_Agent4 68%
    TL1_Agent2 45%
    TL2_Agent3 81%
    TL2_Agent4 23%

  19. I have a cell string that looks like this
    A-1 Mfg. Co., Inc.
    I want to extract the 1st 6 characters into another cell excluding hyphens, spaces and commas so my output looks like this
    A1MfgC
    How do I do the formula?

  20. Hello.

    Does anyone have a formula for extracting the "A1466" in this example?

    MacBook Air Core i5 A1466 13 1.8GHz 8GB 128GB 2017

    The number of characters after the "A" will always be either 4 or 5 characters long however, there will sometimes be other words in the string that start with "A". In this case "Air". The "A" number is also not always in the same position in the cell.

    In fact, rather than extracting the number what is actually ultimately required is to clean the string so that it will result in eg (without the "A" number)

    MacBook Air Core i5 13 1.8GHz 8GB 128GB 2017

    Is this achievable with any kind of search/ substitute type function?

    Thanks in advance.

    1. If I understand your task correctly, the following tutorial should help: Excel Regex to remove certain characters or text from strings.
      Use this pattern:

      '\A\d{4,5}

      I'd recommend you to have a look at our Regex Tools. It can find, extract, delete, or replace strings that match the regex pattern you entered. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  21. I am trying to extract parts of a date in a cell to format it differently on a different sheet.

    Starting with a cell containing "2023-03-11T14:07:58", "I need to reformat this as 2023-03-11 14:07:58 UTC"

    Can I do this by combining some of the functions on this page? Or is there another way?

  22. I want to extract the flat and Tower in this example is denoted by TL which comes right after the cheques from the left. Is there anyways to get it right?

    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007747~902620102 CHQ. NO:007747 23062026972504242645 - AE0190095
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007746~902620102 CHQ. NO:007746 23062026972504242643 - AE0189708
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007724~902620102 CHQ. NO:007724 23062026972504242638 - AE0189619
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007723~902620102 CHQ. NO:007723 23062026972504242626 - AE0189194
    CLEARING CHEQUESF1203 TL19 DEFAULT - CHQ. NO: 000131~302620177 23062026023062001250 - AE0173816
    CLEARING CHEQUESF408 TL7 DEFAULT - CHQ. NO: 000097~102620128 23062026023062001249 - AE0173462
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007722~902620102 CHQ. NO:007722 23062026972504242630 - AE0141381
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007720~902620102 CHQ. NO:007720 23062026972504242633 - AE0141371
    CLEARING CHEQUESIN-HOUSE CHEQUE TRANSFER CHQ. NO: 007726~902620102 CHQ. NO:007726 23062026972504242605 - AE0141335
    OUTWARD CLEARINGF603 TL5 DEFAULT - ~2549518001 CHQ. NO: 000017~804020101 23061026023061001594 - AE0060948
    CLEARING CHEQUEINWARD CLEARING CHQ. NO: 007717~902620102 CHQ. NO:007717 23061100000012570428 - AE0012593

  23. I have a list of TEXT, over 90,000 lines . I copied from a webpage, approx 25,000 names and added it to my list. Now when I sort the list alphabetically, it does not work properly. For example the following is an extract
    ABLAZING GRACE
    ABLE BEAUTY
    ABLE HIT
    ABLE IVY
    ABLE LASS
    ABLE LOTTY
    ABLE MAGPIE
    ABLE MILLIE
    ABLE QUEST
    ABLE RAMON
    ABLE SABLE
    ABLE TO RUN
    ABLE VIVA
    ABLE BONNIE
    ABLE CUSTOMER
    ABLE FAME
    ABLE LANE
    ABLEBE

    You will see that ABLE BONNIE, ABLE CUSTOMER, ABLE FAME and ABLE LANE are not in the correct order. These are what I added from a webpage. Interestingly, if I retype the name and resort my list, the retyped value is sorted correctly.

    I cannot possibly spend the hours to retype all these names - does anyone have a solution - I have tried everything I know ! Thank you

  24. Hi,

    I am trying to extract the Last Name - "Twain" from this data:

    Shania Twain\ShanTw00

    Is there an easy way to do this with a formula?

    1. Hi!
      Please read the above article carefully. Try to use information in this article: How to extract text before a specific character and How to extract text after character.

      =RIGHT(LEFT(A2, SEARCH("\",A2)-1), LEN(LEFT(A2, SEARCH("\",A2)-1))-SEARCH(" ", LEFT(A2, SEARCH("\",A2)-1)))

  25. Hi! I have the following data set and I need to extract weather the model is "RHS" or "LHS" using only =SEARCH

    RHS FRONT SEAT ASY 2WAY COMP
    FRONT SEAT ASY COMP RHS 2WAY
    FRONT SEAT ASY COMP LHS 4WAY
    RHS FRONT SEAT ASY COMP 6WAY

    Many thanks!

    1. Hi!
      I don't quite understand what you want to extract. Give me an example of the result you want to get. But in any case, you need to use other Excel functions besides the SEARCH function. Please read the above article carefully.

  26. 23.13 1/17/2023 P64634 01 DOE, JOHN
    123.14 1/27/2023 6463401 DOE, JASON ALLEN
    3.14- 12/15/2023 F 6463401 DOE, JASON ALLEN

    Could use some help if anyone can. Needing a macro or formula that will put this one cell of data usually in Column A into five different columns. Each line of data is a dollar (sometimes a negative after the value), date, account number (sometimes the is a letter that is separated in front or there is a digit or two separated at the end), and then a name. Always Last, First (sometimes the middle name or initial is there). Text to column won't work since there are not always the same width in between the different data types and sometimes the account number has the space in the beginning and/or the end. Any help would be appreciated. Thanks! :)

    1. Hi!
      Your text strings do not have a single pattern or a single unique delimiter with which to separate text by columns. I'm really sorry, we cannot help you with this.

  27. Hi,
    I wondering if you could help me out with this.

    I have a column with alphabets "A","B","U" and blanks . "A" means Main part ; "B" means Sub Part; "U" means Miscellaneous.
    I want the descriptions of the alphabets in the next column. Is there a way?

    Many thanks!

      1. Thanks very much!

  28. Hi ! Thank you for all the information.

    My case is a bit tricky.

    I want to extract the address of a string of many different characters.
    Eg - From:

    “ [ { code: “ABC” } ; { value : “123” } ; { name : “ABC” } ; { code: “XYZ” } ; { value : “&$)
    ” } ; { Address : “5 Conrad Street 2710” } ; { code dit : “JDI<-” } ; { value : “123” } ; { name : “$;&;7:?” } ;] “

    I want to only have the address - 5 Conrad Street 2710.

    Note that the number of characters of the address will vary. But the characters right before and after will be the same
    Before : Address : “
    After : ” } ; { code dit :

    Is there a way to do that ? Thank you !

    1. Hello!
      Use the advice from the article above. Extract text after certain characters. Then, in the resulting text string, extract the text before certain characters.
      Try this formula:

      =LEFT(RIGHT(A2,LEN(A2)-SEARCH("Address : ",A2)-10), SEARCH("} ; { code dit :",RIGHT(A2,LEN(A2)-SEARCH("Address : ",A2)-10))-3)

  29. I have a cell A2 that has ABC COMPANY #332; DEF COMPANY #254. I need to extract the 4 character numbers #332 and #254. I would like multiple columns to pull in each instance. I can successfully get the 1st instance using the formula

    =TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("#",A2),LEN(A2))," ",REPT(" ",4)),4)) will result in #332

    How can i pull in the 2nd instance #254 in another column?

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

      =MID(A2,SEARCH("$", SUBSTITUTE(A2,"#","$",2)),4)

      1. Thanks, this is working. is there a way to add a qualifier to only return values that have a # followed by numbers and NOT include any instances where a # is followed by letters?

        Example

        #332; #FGH; #254

        Return #254 instead of #FGH

  30. Please help on how to extract or split if the start of the characters are the same?
    Example I want to extract only the words after the second "20L" which is ERASE on below.
    9D21461T5580739.11PHMC1020LP4ETR20LERASE

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

      =MID(SUBSTITUTE(A2,"20L","#",2), SEARCH("#",SUBSTITUTE(A2,"20L","#",2))+1,50)

  31. What about if the two characters are different? Which character do you put where?
    For example I am looking to to pull out just "240" from the string "200- COGS : 240 - Prof Services Mgmt" in a different cell. In this example, I'm looking for the section of the string between the "-" character and ":" character.

    I'm using the formula =MID(F2, SEARCH(":",F2) + 1, SEARCH("-",F2,SEARCH("-",F2)+1) - SEARCH(":",F2) - 1), but I think I need to change around the "-" and ":", but not sure where. I am always needing to break out these 4 pieces of information from a string like this and it takes forever. So if someone could tell me the best formula to use to get each of the pieces of information (200, COGS, 240, Prof Services Mgmt), that would be really helpful!
    Thanks!

  32. Hi,
    Can you please help in excel?
    I want to extract the date from a cell and is it possible the word "Date" would be appearing every time just before the date.
    I extracted the last 8 characters that contains date (Example of that text --> JSB-EPP-005253-23052022) but the requirement of the format is not achieved. Last 8 characters should be displayed in the assigned cell as "Date: 23.05.2022)".
    If this is possible, so please guide me how it would be implemented.
    Appreciate your great work,

    Thanks & Warm Regards,
    Ali

      1. Thank you very very much...this Formula worked great and fulfilled my all requirement :)
        I once again appreciate and bundle of thanks Dear Sir..

  33. if I have sentence in one cell, for example :
    1. I want to got to "Market"
    2. Yesterday, "Old lady" passed away
    3. "Car" is expensive

    How do you take only the word in between " " sign and copy to other cell (in this case word : Market, Old lady, Car)

    Thank you

    1. Hi!
      Pay attention to the following paragraph of the article above – How to extract text between two instances of a character. It covers your case completely.

  34. I'm needing to use this for a set of data that sometimes has multiple words in a cell, but not always. I've tried nesting it in an IF formula, but I've gone wrong somewhere. What I have works if there are spaces, but not if there aren't.

    Sample data set:
    Cat
    Brown dog
    Mixed-breed dog

    I would like to get the following results:
    Cat
    Brown
    Mixed-breed

    This formula (where A16 is the original cell) works for the second two, but not the first.
    =IF(SEARCH(" ",A16),LEFT(A16,SEARCH(" ",A16)-1),A16)

      1. That's worked! Thank you!

  35. Hi...!!
    I want to check a cell by formula "if a cell contains FORMULA in itself or not".
    I am using Excel 2010 so "ISFORMULA" function is not workable for me.

    Please advise.

    1. Please advise

  36. Hi

    I hope you can help me with this kind sir

    In column one, I have a list of tracking numbers that looks like this:

    123456
    123456
    123456
    123456
    12345678
    12345678
    12345678
    1234567
    1234567
    12345
    12345
    12345

    Suppose all FedEx trackings are 7 digit numbers, USPS are 8 digits length, DHL are 6 and XPO are the ones with 5 digits.
    What I'm trying to do is finding all USPS trackings and extract em from colum 1 to column 2, then, find all FedEx trackings and extract em to column 3, then find all the DHL tracking numbers to column 4 and so on.
    Is there a formula for that? Like, one that find all values of a given amount of digits (or characters) in a column and list em in a different column?

    Thanks in advance!

    (Sorry for the spam, I wasn't sure if I have replied to someone's comment lol)

  37. I am looking to extract the text after MA_

    RURAL_BUILD_FTTP_28B_FTTP_MA_PCPV9135_ON69848_AGN_SPN_CBT_RURAL_VA1
    RURAL_BUILD_28B_FTTP_MA_PCPV9152_POLING_CIVILS_VA1
    RURAL BUILD FTTP 30A_FTTP_MA_PCPV9058_MICS_CIVILS_VA1
    RURAL BUILD_29A_FTTP_MAIDSTONE_MA_PCPV9027_TEST ROD AND ROPE

    Ideally I would like a return of only the V*** after PCP

    Any help would be greatly appreciated

  38. Please How do i extract group of numbers appearing in in different positions of a different cells.
    Example;
    A
    ABSTV234 K:50s
    sg789nvhn092h
    satcads15qw20

    B(outcome)
    234
    789
    15

  39. "1970000.
    00"
    how to remove 1st and last 4th char in excel. i excel sheet it is hot showing same.
    in excel is display as 1970000.00
    i have tried right, left, char, find & replace, int, roundup & etc.
    pls help me it is taking lot of time.

  40. Hi Dear,

    Can you please help me to extract the text using excel logic

    791541213823202211Towage10120
    80292721355020228Charts/Publications10.11
    80292721355020228Class Certificates / Survey fee (DNV, Lloyd's, GL) / ISM250
    80292721355020228Port Costs1897
    80292721355020228Port Costs2116.8
    80292721355020228Port Costs4.7
    80292721355020228Port Costs66.69
    80292721355020228Waste Disposal1639.28
    90301491169020229Mooring Unmooring1003.89
    903643010118Federal Goods and Services Taxes (GST)1633.41
    903643010118Federal Goods and Services Taxes (GST)1834.4
    903643010118Federal Goods and Services Taxes (GST)2035.4
    903643010118Federal Goods and Services Taxes (GST)218.28
    903643010118Federal Goods and Services Taxes (GST)230.02

  41. Hi All,
    not even sure if this is possible. But i need to return the first 5 digit number from the below alphanumeric text in a cell. the answers should be
    Example 1: 93423
    Example 2: 87952

    Example 1: "**02.06 return updated in SPA** ordered 93423 BR 4PNS PCFC A RK 495L 1X1 x 2 delivered 2 x 4Pns Pl Al 94253 1300353110"
    Example 2: noted with d Short delivered multiple invoices - 2 x BR Vct Br NGB 750 4x3 87952 12 x BR PI N A 5.1% NGB 330ML 4X6IMP 94152

  42. 1. Having input with total four remarks “Received, waived, OTC due, PDD due”
    a. In Column “A" I have number of documents with document description
    b. In Column “B" I have dropdown of Copy or Original
    c. In Column “C" I have Remark of "Received" for Respective document
    d. In Column “E" I have Remark of "Waived" for Respective document
    e. In Column “F" I have Remark of "Short due" for Respective document
    f. In Column “G" I have Remark of " Long due" for Respective document

    2. Outcome required
    a. In Column “A" I required document description either in copy or original having remarks “Received” (1st scenario)
    b. In Column “A" I required document description either in copy or original having remarks “Short due & Long due” (2nd scenario)
    c. In Column “B” I required document nature with outcome of copy or Original as per input data (Required in both scenario)

    Please help here to extract the outcome it will be great help for me thanks in advance

    1. Hi!
      I am not sure I fully understand what you mean. You want to put the result in column A, which already has data. Please provide me with an example of the source data and the expected result.

      1. Point no 1 Narrate about it's source data through subhead which was inputted in sheet no 1

        & point no 2 narrate about required outcome through source data in sheet no 2 therefor I mention column A & B in both Point because these refer two different sheet. I mention the sample data below for sheet 1. please update excel formula which i can use in sheet 2 of same excel.

        DOCUMENT TYPE Received Waived Short due Long due
        sale deed Copy Received
        RD Copy Waived
        Death certificate Copy Waived
        sale deed 2 Copy Received
        ATS Original Short due
        SPA Original Short due
        sale deed Original Long due
        RD Original Long due
        Death certificate Original Long due

        1. Due to some limitation unable to paste excel sheet therefor i mention revised source data for sheet one plz copy & paste the data in excel sheet & share the formula in sheet two
          a. In Column “A" of sheet 1

          DOCUMENT (Heading)
          sale deed
          RD
          Death certificate
          sale deed 2
          ATS
          SPA
          sale deed
          RD
          Death certificate

          b. In Column “B" of sheet 1
          TYPE (Heading)
          Copy
          Copy
          Copy
          Copy
          Original
          Original
          Original
          Original
          Original

          c. In Column “C" of sheet 1
          RECEIVED (Heading)
          Received
          BLANK
          BLANK
          Received
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK

          d. In Column “E" of sheet 1
          WAIVED (Heading)
          BLANK
          Waived
          Waived
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK

          e. In Column “F" of sheet 1
          SHORT DUE (Heading)
          BLANK
          BLANK
          BLANK
          BLANK
          Short due
          Short due
          BLANK
          BLANK
          BLANK

          f. In Column “G" of sheet 1
          LONG DUE (Heading)
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK
          BLANK
          Long due
          Long due
          Long due

          Outcome required in sheet 2

          1. In Column “A" of sheet 2

          a. I required document description either in copy or original having remarks “Received” (1st scenario)
          b. I required document description either in copy or original having remarks “Short due & Long due” (2nd scenario)

          2. In Column “B" of sheet 2

          c. I required document nature with outcome of copy or Original as per input data (Required in both scenario)

  43. hey

    i have a data i need to do the following

    1- i need to select the word ( black )
    2- i need to delet it from the A1 cell
    3- i need to replasce it in cell b1

    ex.

    A1 iphone 6s black
    A2 iphone black 7p
    A3 black flat charger

    what i want to do is

    A1 iphone 6s B1 black
    A2 iphone 7p B2 black
    A3 flat charger B3 Black

    thank you

  44. Excellent content as usual

  45. xboxonefifa14
    3dspokemonmoon
    playstation3ufcundisputed3
    playstation3djhero
    playstation3fifa14
    xbox360pure
    xboxoneforzahorizon2
    playstation2needforspeedunderground
    playstation4yakuza0
    xbox360worms2armageddon
    playstation4soulcaliburvi
    xboxonehitman2

    Hello, I am looking for a way to split the game platforms; (xbox360, xboxone, playstation2, playstation3, 3ds etc) from the game titels; (fifa14, pokemonmoon, djhero etc). Is there a formula I could use ? I tried this formule: =IF(SEARCH("one";A2;2);LEFT(A2;SEARCH("one";A2)+2);1) to split xboxonefifa14 which only worked to split xboxone from the game title, but I couldn't get the game title in a different column. So my question is how I could be able to separate the game titles from different platforms?

      1. Hi,

        Thanks for the reply! The formula worked for the most games and I managed to separate the games from the game platform. However, I do run into trouble with the game platforms; xboxone, xbox360, xboxseriesx, pc and 3ds.

        xboxonefifa14 fifa14onefifa14
        3dspokemonmoon pokemonmoon3pokemonmoon
        playstation3ufcundisputed3 ufcundisputed3
        playstation3djhero djhero
        playstation3fifa14 fifa14
        xbox360pure pure360pure
        xboxoneforzahorizon2 forzahorizon2oneforzahorizon2
        playstation2needforspeedunderground needforspeedunderground
        playstation4yakuza0 yakuza0
        xbox360worms2armageddon worms2armageddon360worms2armageddon
        playstation4soulcaliburvi soulcaliburvi
        xboxonehitman2 hitman2onehitman2
        playstation3nhl13 nhl13
        xboxseriesxassassinscreedvalhalla assassinscreedvalhallaseriesxassassinscreedvalhalla
        pctransistor transistor
        pclifeisstrange2episode3wastelands pclifeisstrange2episode3wastelanlifeisstrange2episode3wastelands

        These are a few games I tried to seperate. Do you have any idea what might cause this problem and why it doesn't replace the game platform with a blank value and copies the game + gameplatform instead?

        The formula I used: =CONCAT(IF(ISNUMBER(SEARCH($H$2:$H$21;A2)); SUBSTITUTE(A2;$H$2:$H$21;"");""))

        Input for H:
        xboxone
        xbox360
        playstation3
        playstation4
        playstation2
        playstationvita
        playstation5
        wii
        switch
        3ds
        psp
        ds
        gameboyadvance
        dreamcast
        xboxseriesx
        xbox
        gamecube
        pc
        wiiu
        stadia

        1. Hi!
          The names of the gaming platforms have partial matches. If you search for xbox360 it will find xbox360 and xbox. Also "ps" can be found in the name of the game.

  46. FI_AC_BONDS
    FI_AC_OBU_LIQ
    FI_AC_SUKUK
    FI_AC_SUK_OBU
    FI_AFS_BONDS
    FI_AFS_OTH_BUS
    FI_AFS_ST
    FI_AFS_SUKUK
    FI_AFS_SUK_OBU
    UNB_FI_AFS_BOND
    UNB_FI_AFS_SUK

    I want to find if AFS or AC word is available on above table and need to get in displace as AFS or AC in another cell.

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula with SEARCH function:

      =IF(ISNUMBER(SEARCH("AFC",A1)),"AFC",IF(ISNUMBER(SEARCH("AC",A1)),"AC",""))

      If this is not what you wanted, please describe the problem in more detail.

  47. Cell A1 contains Iim Rd, Near Kendriya Vidyalaya, Mubarakpur, Lucknow, Uttar Pradesh 226201

    Cell A2 contains Lucknow

    I need to extract until Lucknow into Cell A3 using a Formula

    Cell A3 FINAL VALUE should be Iim Rd, Near Kendriya Vidyalaya, Mubarakpur ( with or without comma ) is ok

    1. Hi!
      Pay attention to the following paragraph of the article above - How to extract text before a specific character.
      It covers your case completely.

  48. Hello!

    thanks for this very interesting article. Can you help me to solve this problem

    [{ 12, name: Adventure}, { 14, name: Fantasy}, { 28, name: Action}]

    i wana making colum B Adventure; colum C Fantasy and Colum C Action

  49. Rin (Re.1) Advanced Detergent Powder, 10g (Pack of 108) (IA) = 108
    Lays (Rs.10) Potato Chips - Cream & Onion, 30g (Pack of 10) (IA) = 10
    Parachute (Re.1) 100% Pure Coconut Hair Oil, 2.3ml (Pack of 36) (IA) =36
    Too Yumm! (Rs.5) Veggie Stix - Chilli Chatka, 14g (Pack of 12) (IA) =12

    Please help here to extract the number after the word " Pack of"

      1. No sir,

        Its not working ,
        showing blank when i apply this formula.

          1. Hello Sir

            The given formula not working for below cases,

            1.Pampers Baby Dry Pants - M, 2pcs, (Pack of 8)
            2.Colgate (Rs.10) Active Salt Toothpaste, 21g (Pack of 12)

            1. Hi!
              The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. For this data, another formula is needed.

              =IFERROR(MID(A2, SEARCH("Pack of ", A2) + LEN("Pack of "), SEARCH(")",A2,SEARCH("Pack of ", A2)+1)- SEARCH("Pack of ", A2) - LEN("Pack of ")), "")

              1. Supper Sir,

                Great Thanks.....

  50. I am trying to extract the month from a string like the following
    CF Customer Oct 07
    Ash Customer Sep 07
    Pete Customer Sep 07
    Can you give me a formula?

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