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

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

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

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

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

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

  6. 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, 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.

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

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

  9. 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%

  10. 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?

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

  12. 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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi,
    Can you please suggest in this, will appreciate your help.

    I have some cells like below

    Sushama.K.CTS -> Sushama.K.CTS -> Arbina.B.CTS
    Snehal.C.CTS -> Pooja.G.CTS
    Sonam.C.CTS -> Sonam.C.CTS -> Sonam.C.CTS
    Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS

    How I can extract the last name from each cells after the ">" symbol.

    Like, my result for above cells should be like

    Arbina.B.CTS
    Pooja.G.CTS
    Sonam.C.CTS
    Sonali.S.CTS

    1. I have tried with this formula,
      =RIGHT(Q3424,LEN(Q3424)-SEARCH(">",Q3424))

      but it's giving me this result

      Sushama.K.CTS -> Arbina.B.CTS
      Pooja.G.CTS
      Sonam.C.CTS -> Sonam.C.CTS
      Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS -> Sonali.S.CTS

      but I need the last part

      So please suggest.

      1. Hello!
        Replace the last ">" with "#" using the SUBSTITUTE function. Determine the position of this character using the SEARCH function. Starting from this position, extract string from the text using the MID function.

        =MID(SUBSTITUTE(A1,">","#",LEN(A1)-LEN(SUBSTITUTE(A1,">",""))), SEARCH("#",SUBSTITUTE(A1,">","#",LEN(A1)-LEN(SUBSTITUTE(A1,">",""))))+1,50)

        This should solve your task.

  32. Hi, I have a query , it would be great if you could solve this. I have some UPC codes in one cell, i need to copy all the UPC codes into different excel cells.

    077346100626, 011951600003, 781968002106, 692000196342, 885694471981, 715933319937, 199960027704

    I need to copy numbers after every "," sign to different cells.
    Like
    077346100626
    011951600003
    781968002106
    692000196342
    885694471981
    715933319937
    199960027704

    Thanks,

      1. its helpful, but it didn't solve my problem. In the tutorial which you told me, it find the characters in a three word cell, but in my case i have more than 5 or more words separated by ","
        How can i separate them by this formula ?
        in the tutorial it used three functions 1-Left 2-Mid 3-Right
        Is there any thing else i can use to solve this problem ?

  33. Hello!
    If I understand the problem correctly, you can use the SEARCH function to search for the desired word. With nested IF functions, you can check two conditions.
    Please try the formula below:

    =IF(SUM(--ISNUMBER(SEARCH({"UP","BULISCH","BUY"},A2))) > 0,$D$2, IF(SUM(--ISNUMBER(SEARCH({"SELL","BEARISH","DOWN"},A2))) > 0,$E$2,""))

    I hope it’ll be helpful. If something is still unclear, please feel free to ask.

    1. Pozdrav !
      OK Pomaze Thanks
      Goran

  34. Hello Alexander,

    I hope you can help me, I have a list of transactions in column J and a list of text in a separate tab in the same workbook. I want to write a formula to find this text in the separate tab and extract this from the transactions (Column J) and the result should be in Column N.

    For Example:

    Column J

    "Int Debit Order To Settlement
    "Cashsend Digital SettlementCard No.
    Digital Tran Fees Settlement *
    Bal Brought Forward
    Monthly Acc Fee Headoffice *
    Transaction Charge Headoffice *
    "Int Debit Order To Settlement"
    "Acb Debit:External Settlement 19.75 "
    "Digital Transf Cr Settlement"
    "Atm Withdrawal 31.50 TCard No. "
    "Pos Purchase Settlement 4.35 TCard No. (Effective 16/05/2022)"
    "Notific Fee Sms Notifyme 1.20 T2 Sms Notifications"
    PY 4654654654Transfer

    Separate Tab List in Column Q:

    Notific Fee Sms
    Bal Brought Forward
    Pos Purchase
    Acb Debit:External
    Transaction Charge
    Monthly Acc Fee
    Cashsend Digital
    PY 4654654654
    Int Debit Order To
    Digital Tran Fees
    Atm Withdrawal

    In Column N:

    I need the formula to search from the list (Separate Tab) and extract the text from the transactions in Column J.

    If you could assist me with this formula please.

  35. Hi

    Please resolve my below problem:

    I Have data in cells as below:

    A1= 1B90
    A2= 1B113
    A3= 3DE- 61
    A4= 1E-105

    And I want as below:

    B1= B
    B2= B
    B3= DE
    B4= E

  36. Hello,

    I have the following statement where I'm trying to pull the numerical value's off the far right side (a: 157.00; b: -0.23; c: 9.00). I have tried the example up the top for RIGHT function but it jut pulls everything excluding the month (Feb). If I drop the LEN function and just use SEARCH it pulls only four characters from the right (a:7.00; b:0.23; c: 9.00).

    Feb 07 Mudgee Vet Surg Mudgee Au 034254325786123489 157.00
    Feb 06 Temple & Webster St Peters Au 25643965992308034556 -0.23
    Feb 05 The Brumby Nepalese R Nadal Au 75123502038260262235719 9.00

    Thank you for your help in advance.

    Cheers,
    Rowie

  37. Dear Sir,
    I have texts in a large xls in the pattern of
    APLHA|Alphanum1|alpha num2|alpha num3 - the length of each alphanum is not fixed. Text has spaces too.

    I need to get all the text upto the last | delimiter - i.e. - "APLHA|Alphanum1|alpha num2"

    Could not make any of the above work. Please assist.

    1. Hello!
      Use the SUBSTITUTE function to replace the last delimiter with another character. Calculate the position of this character using the SEARCH function. Get the required number of characters using the LEFT function.

      =LEFT(A1,SEARCH("#",SUBSTITUTE(A1,"|","#", LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))))-1)

      I hope my advice will help you solve your task.

  38. Primary Street Stored in A1
    560, 95, Kanpur Road, Krishna Nagar Village, Krishna Nagar, Alambagh, Lucknow, Uttar Pradesh 226023

    Primary City Lucknow Stored in A2

    a3 should retun text to left of Lucknow ( a2 )

    ie., 560, 95, Kanpur Road, Krishna Nagar Village, Krishna Nagar, Alambagh,

    Thanks in advance

      1. Thanks Brother, it helped

  39. Hello
    if i cells with:
    111222
    11222
    1222

    and i want extract "1" from them like this:
    111
    11
    1

    how i can do it?

    1. Hello!
      Use substring functions to extract text from cell:

      =LEFT(A1,SEARCH(2,A1)-1)

      Hope this is what you need.

  40. Hi can you kindly help me, i have a situation where in a cell there is a few items that i need to separate out into different columns

    "Item 1. D/W RSC (CTN 35)
    Material Code: -
    Quantity: 1,000 piece
    Account Name (GL): OPS PACKING MATERIALS (4340000)
    BusA/CC: AM71/AM2D
    Mandatory to Quote: Yes

    Item 2. D/W CTN (CTN 90)
    Material Code: -
    Quantity: 1,000 piece
    Account Name (GL): OPS PACKING MATERIALS (4340000)
    BusA/CC: AM71/AM2D
    Mandatory to Quote: Yes

    Item 3. D/W CTN (CTN 98)
    Material Code: -
    Quantity: 2,000 piece
    Account Name (GL): OPS PACKING MATERIALS (4340000)
    BusA/CC: AM71/AM2D
    Mandatory to Quote: Yes

    Item 4. D/W RSC (CTN 56)
    Material Code: -
    Quantity: 1,000 piece
    Account Name (GL): OPS PACKING MATERIALS (4340000)
    BusA/CC: AM71/AM2D
    Mandatory to Quote: Yes

    Item 5. S/W RSC (CTN 28)
    Material Code: -
    Quantity: 1,000 piece
    Account Name (GL): OPS PACKING MATERIALS (4340000)
    BusA/CC: AM71/AM2D
    Mandatory to Quote: Yes

    ( This is all in 1 cell)
    How do I separate them
    I'm in desperate need, please help me, thank you

    1. Hello!
      If you want to split text into cells by line break, you can use the Excel tool - Text to columns. Use the key combination CTRL+J to specify line feed as "other" character.
      You can also use the Split Text tool. It provides many options to split text into cells. 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.

      1. is there any formulas that i can use?

  41. Hello!

    we have columns of data in each - mention below.
    Please help us to segregate M followed by 8 digits in a separate cell.
    -------------------------------------------------------------
    "3 laptops

    Po# m17121848 item # 9999207718877
    Po# m18147289 item # 9999207707199
    Po# m34283154 item # 9999207718875"
    -------------------------------------------------------------
    "p.o m31153508
    item number 9999207495586"
    -------------------------------------------------------------
    "p.o. m355515737
    item number 9999207564615"
    -------------------------------------------------------------
    "p.o m31153508
    item number 9999207495586"
    ------------------------------------------------------------
    "p.o m31153508
    item number 9999207495586"
    -------------------------------------------------------------

      1. Thanks for the response. The above formula is working for Single M in single cell but we have more of multiple M************** in single excel cell. can you please us to provide mulitple M formula.

          1. we want to extract multiple occurrence of M with 8 digits only in single cell data. for example

            we have data in a single cell of below

            Po# m17121848 item # 9999207718877
            m18147289 item # 9999207707199
            Pom34283154 item # 9999207718875"

            we want to get in a cell like below:
            M17121848
            M18147289
            M34283154

            Thanks for your help.

          2. i want to get M followed by 8 digit. i have multiple occurrence Mxxxxxxx in a single cell data. on this formula (=MID(A2,SEARCH("m",A2),9)) work for single occurrence of M followed by 8 digits please help me to get all other occurrence of M.

        2. For example:

          Below data available in single excel cell.

          Po# m17121848 item # 9999207718877
          m18147289 item # 9999207707199
          Pom34283154 item # 9999207718875"

  42. Hi,

    I have this data and I need to extract only the characters under the format M9xxxxxxxxxx.

    M90000000001; 1062172; 4503260578
    M90000000002; L20000000001; M90000000005
    M90000000003

    I am using the formula: =MID(A2,FIND("M9",A2,1),12) but this does not bring all the data, in case I have 2 values in a single cell M9xxxxxxxxxx (i.e. line 2).

    Is there a way to extract both?

    Thank you!

      1. Thank you for the suggestion!

  43. Hello!
    I have data in this format under excel sheet.
    I only want to extract characters from the below column, how do I leave/remove special characters (unwanted symbols) and only extract characters which are present in each row.
    Please help me..

    location

    Yakima, WA
    Distrito Federal, México
    Prison
    ?
    laugh of january
    Karachi, Pakistan
    kiwook. ♡
    grace, she/her, 24
    QATAR
    eve ♡ [swe/eng/한]
    Bengaluru, India
    Saudi Arabia .Taif
    kiwook. ♡
    Lab of Womb
    Nederland
    Calabria, Italia
    Iraq
    Alexandria/Tanta
    #everywhere
    Ruwa Zimbabwe
    Reality
    Ayodhya
    Maldives
    ??
    Szczecin, Polska
    Hargeisa, Somaliland
    Prosthodontics Section, CoD
    Davao City, Davao Region
    under these bitches’ skin
    Islamabad, Pakistan
    ??
    khandwa mp
    New York, NY ✈️ Houston, TX
    United Arab Emirates

    Atlantis
    Lampung, Indonesia

    08/30/17

    Jakarta Pusat, DKI Jakarta

    losers club
    tyler williams inc
    Kharkhoda, India

    Iraq

    Tweets are my own and not representative of my employer

    Philippines

    Jeddah - khartoum
    Madinah

    1. Hello!
      To remove special characters from text, you can use regular expressions. You can find the examples and detailed instructions here: Excel Regex to remove certain characters or text from strings.
      You can use this formula:

      =TRIM(RegExpReplace(A5, "[^0-9a-zA-Z\.,/ ]+", ""))

      You can also use Regex Tools for Excel. With Regex Tools, which are part of Ultimate Suite for Excel, you can find, extract, remove, or replace strings that match a pattern.

  44. Hello, I have a task to extract a specific location code from a string. Examples from the text I am using are as follows:
    AUS177-4M
    Canada551-3W
    MEX316-3W
    US160-3Mo
    ARG265-2W
    MEX363-5Mo
    US351-4W
    GER195-6Mo
    GER529-2Mo
    AUS301-7Mo
    GER60-3W
    ENG102-8Mo
    AUS219-9W
    ENG342-10Mo
    US476-11M
    GER93-6W
    GER442-10M
    Japan17-8W
    Canada559-11Mo
    ARG389-11Mo
    Canada121-12M

    As you can see the data aren't neatly arranged, nor are they all a set amount of characters long. I need to be able to extract the location text (Canada, GER, US, Japan) exactly as it appears in the text string and display it in another column. Is there any way you could assist me in this?

    1. Hello!
      To extract all characters up to the first digit in the text, try using the formula

      =LEFT(A2,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A2)), "",FIND({1;2;3;4;5;6;7;8;9;0},A2)))-1)

      Hope this is what you need.

      1. If I just wanted to display the number or letter(s) at the end of a string, would this approach work also? For example, If I had HSM-11Mo, and I wanted to display just the 11 or the Mo inside of a column by itself, would I be able to with this type of formula? I am not an excel expert by any means so I apologize for any confusion in my statement.

        1. Hello!
          If you want to show all characters after the last digit, then try this formula:

          =MID(A2,LOOKUP(,-MID(A2,ROW($1:$99),1),ROW($1:$99))+1,50)

          1. This formula works on most of the cells in my data set but a few of them still include the dash and number. For example, the data
            NGE270-18M
            SUA110-5M
            EXM390-18Mo
            NGE430-17W

            would all return a -18M or -18Mo or -17W depending on the cell. Is there anyway I can fix this?

  45. I have data in this format under excel sheet.

    S02E01.the.Wild.Goose.Chase
    S02E02.Needle.in.a.Haystack
    S02E03.Might.of.the.Atwal.Empire
    S02E04.True.Lies
    S02E05.Wedding.Bells.Make.a.Loud.Sound
    S02E06.Revenge.Is.Best.Served.Cold
    S02E07.the.Girl.and.the.Cop
    S02E08.Goons.Guns.and.Bombs
    S02E09.the.Hunter.Is.Now.the.Hunted
    S02E10.Thats.the.Way.the.Cookie.Crumbles

    I want result as any data excluding starting 7 characters.

    Thanks

  46. Hello

    I would appreciate any help. I have a high volume of the below data (located in one cell);

    "Winning Combination: 2/1/1/1/1/2/1/1/3/1
    Status: Official
    Results: (9/10)
    Winners: 2015.43
    Dividend: R42.60
    Results: (10/10)
    Winners: 141.00
    Dividend: R2,439.80"

    I need to extract;

    2015.43
    42.60
    141
    2439.80

    Any advice?

    Thanks

  47. what formular can i use to extract only characters in a cell without the LAST 4 DIGITS
    e.g "Vitamin A supplementation 6-11 months 2019" results should be "Vitamin A supplementation 6-11 months"
    and
    "LLIN given to pregnant women 2021" results should be "LLIN given to pregnant women"

    1. Hello!
      Extract all characters from the text, except the last four. Use the LEFT and LEN functions.

      =LEFT(A1,LEN(A1)-4)

      I hope I answered your question.

      1. it worked!!, thanks

  48. Hi,

    I have cells containing this pattern:
    A.BBBBB
    A.B.CCCCCCC
    A.B.C.DD

    How do I get the substring to the right of the utmost right period, e.g. BBBBB, CCCCCCC, DD?

    TIA!

      1. Thanks Alexander, this helps a lot, though not completely, since some of the strings look like A.B.C.DD-EEE and it's DD-EEE that I'd like to extract.

          1. You're super. Thanks a lot!

  49. Hey, How can I extract from cell containing 4c,5c,9e,10z,12c the words containing c like, the extraction of above should look like- 4c,5c,12

      1. Thanks, But is there anyway of implementing this into Google Sheets?

        1. Hello Siddhant,

          RegExpExtract doesn't work in Google Sheets but you can try this formula instead:
          =ArrayFormula(TEXTJOIN(",",TRUE,IFNA(REGEXEXTRACT(SPLIT(A1,","),"\d+c"))))

  50. Olá,

    Eu gostaria de extrair para outra coluna o 3880-109 Ovar do seguinte texto; Zona Industrial de Ovar, Loja n.º L 00.013, Av. Dom Manuel I, 3880-109 Ovar.

    Como faço isso?

    Obrigado

    1. Hello!
      I believe the following formula will help you solve your task:

      =MID(A1,SEARCH("#", SUBSTITUTE(A1,",","#",LEN(A1)- LEN(SUBSTITUTE(A1,",",""))))+2,50)

      1. Obrigado Alexander Trifuntov pela pronta ajuda, infelizmente meu excel é de 2007 em português e a fórmula dá erro.

          1. continua a dar erro na fórmula

            1. Hi!
              Perhaps you do not use a comma, but a semicolon as a separator in the formula. It depends on the regional settings of your Windows.

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