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

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

Comments page 3. Total comments: 447

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

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

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

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

  5. Pozdrav !
    Kako izvuci jednu rec iz jedne celije u kojoj ima vise reci ? Evo nekoliko primera celija iz kojih treba izvuci odredjene reci :
    H1 BRENT_OIL at 02:55:02 SuperTrend up -(jedna celija)
    3_Level_ZZ_Semafor_NRP Alert on CrudeOIL, period M15: SELL SIGNAL Level2 -(jedna celija)
    Apollo Scalper CADCHF M15 BUY @0.74719, TP 0.75019, SL 0.74569 -(jedna celija)
    Problemi : Jedna trazena rec ima vise znacenja : (UP,BULISCH,BUY) Drugi primer : (SELL,BEARISH,DOWN).
    Trazena rec nije uvek na istom mestu u celiji. Kako postaviti formulu koja jednostavno pita prvu celiju da li se u njoj pojavila jdna trazena rec : (UP,BULISCH,BUY) ili (SELL,BEARISH,DOWN) Postavio bih formulu za prvo pitanje u recimo D2 a za drugo E2. Ako se bilo koja rec od trazenih pojavi moze se napisati u istoj celiji gde je postavljena formula ili nekoj drugoj svejedno... Tako bih sa te dve kolone pratio signale koji se pojavljuju u prvoj koloni ? Svasta sam probao pa sam se zamrsio izgleda jednostavno ali je zapetljano...Primeri koje sam koristio pa mi nije bas sve uspelo :
    =IF(SEARCH("UP",D2),G10,H10) =IF(ISNUMBER(SEARCH("UP",D2)),G10,H10)
    =IF(SUMPRODUCT(-ISNUMBER(SEARCH("UP",D2:D2))),"0","UP") =TRIM(MID(D2,SEARCH("UP",D2)+10,LEN(D2)))
    =IF(ISNUMBER(SEARCH("supertrend",D2)),G2,"")&IF(ISNUMBER(SEARCH("apollo scalper",D2)),G3,"")
    Itd... Hvala
    Goran

    Hello!
    How to extract one word from one cell that contains several words? Here are some examples of cells from which certain words are extracted:
    H1 BRENT_OIL at 02:55:02 SuperTrend up - (single cell)
    3_Level_ZZ_Semaphore_NRP CrudeOIL warning, period M15: SALES SIGNAL Level 2 - (single cell)
    Apollo Scalper CADCHF M15 BUY @ 0,74719, TP 0,75019, SL 0,74569 - (single cell)
    Problems: One search word has several meanings: (UP, BULISCH, BUY) Another example: (SELL, BEAR, DOWN).
    The search word is not always in the same place in the cell. How to set up a formula that simply asks the first cell if one of the search words appeared in it: (UP, BULISCH, BUY) or (SELL, BETTER, DOWN) I would set the formula for the first question in say D2 and for the second E2. If any of the words of the requested occurrence can be entered in the same cell where the formula is located or someone else doesn't care ... So, I would follow the signals that appear in the first column from those two columns? I tried everything, so I was confused, it looks simple, but it's complicated ... Examples I used, so I didn't succeed:
    = IF (SEARCH ("UP", D2), G10, H10) = IF (NUMBER (SEARCH ("UP", D2)), G10, H10)
    = IF (SUMPRODUCT (-UM (SEARCH ("UP", D2: D2))), "0", "UP") = TRIM (CENTER (D2, SEARCH ("UP", D2)) + 10, LEN D2)))
    = IF (ISBROJ (SEARCH ("supertrend", D2)), G2, "") & IF (ISBROJ (SEARCH ("apollo scalper", D2)), G3, "")
    Etc ... Thanks
    Goran

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

  6. Super helpful. Thanks

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

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

  9. Hi

    I need to extract whatever string is between the 3rd and 4th undercover of the texts below:

    asd_KSA_Consideration_LocalFootball_Beverages_Core 18-34_Trueview_Youtube_CLE_February2022_1546960|1862997

    Here the answer should be "LocalFootball"

    asd_KSA_Awareness_LocalFootball_Beverages_Core 18-34_Bumper Ads_Youtube_CLE_February2022_1546960|1440241

    Here the answer should be "LocalFootball"

    asd_KSA_Awareness_LaunchKSA_Beverages_CoreAudience_Bumper Ads_Youtube_CLE_BLS_March2022_1918871|1311320

    Here the answer should be "LaunchKSA"

    asd_KSA_Consideration_LaunchKSA_Beverages_InMarket_Discovery Ads_Youtube_CLE_BLS_February2022_1918871|1740112

    Here the answer should be "LaunchKSA"

    asd_KSA_Awareness_LaunchKSA_Beverages_CoreAudience_Video_DV360_PGD_Adintop_February2022_1918871|1014779

    Here the answer should be "LaunchKSA"

    asd_KSA_Conversion_LaunchKSA_Beverages_CoreAudience_Video_DV360_PGD_DMS_February2022_1918871|1883124

    Here the answer should be "LaunchKSA"

    asd_KSA_Consideration_LaunchKSA_Beverages_CoreAudience_Trueview_Youtube_CLE_BLS_February2022_1918871|1362081

    Here the answer should be "LaunchKSA"

    asd_KSA_Conversion_Launch_Beverages_CoreAudience_Video_DV360_PGD_Vidmatic_March2022

    Here the answer should be "Launch"

    asd_KSA_Conversion_Launch_Beverages_CoreAudience_Video_DV360_PGD_Vidmatic_March2022_Cancelled

    Here the answer should be "Launch"

    asd_JOR_Consideration_Max_Beverages_Core 18-35_Trueview_Youtube_CLE_March2022_OD22|3228

    Here the answer should be "Max"

    asd_JOR_Awareness_Max_Beverages_Core 18-35_Bumper Ads_Youtube_CLE_March2022_OD22|3228

    Here the answer should be "Max"

    asd_KSA_Awareness_CFBQ1_Beverages_CoreAudience_Video_DV360_PGD_MMP_February2022_1963621|1373446

    Here the answer should be "CFBQ1"

    asd_KSA_Awareness_CFBQ1_Beverages_CoreAudience_Video_DV360_PGD_DMS_February2022_1963621|1564216

    Here the answer should be "CFBQ1"

    asd_KSA_Consideration_CFBQ1_Beverages_CoreAudience_Trueview_Youtube_CLE_BLS_February2022_1963621|1508651

    Here the answer should be "CFBQ1"

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

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

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

  13. I need to find the string before "-DT" or "-LT" from following

    QDY3-DT-HC00121
    ZYN-LT-000013
    CN-URB-LT-00036A23
    WILT-DT-LPD001

    so the results would looks as follows

    QDY3
    ZYN
    CN-URB
    WILT

    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.

      =LEFT(A2, IF(ISNUMBER(SEARCH("-DT",A2)-1),SEARCH("-DT",A2)-1,SEARCH("-LT",A2)-1))

      To use two search conditions, use a nested IF function.

      1. Thanks for the response, it works perfectly. also I referred the above mentioned paragraph but still couldn't understand logic being used in this formula :( . would you be able to explain your formula. also if I want to add one more string "-SRV", what would be the new formula. hope that would help me to understand the logic

        1. Hi!
          To search for multiple variants of a specific character, you can use the formula

          =CONCAT(IFERROR(LEFT(A2,SEARCH({"-SRV","-DT","-LT"},A2)-1),""))

          The IFERROR function will replace failed search errors with empty strings. The CONCAT function converts an array of search results into a single text string.

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

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

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

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

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

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

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

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

  22. Hi there. I have a column with notes where I want to extract the 15 digits that appear after IRCT (or irct), including the IRCT into an adjacent (blank) column. The IRCT number can appear anywhere in the cell.
    E.g. (2022-02-09 16:24:37)(Select): irct2012042495523N1;
    or IRCT2017011520145N4; (2021-09-01 15:31:36)(Screen): #66 Abdollahi 2019 might be linked & has abstract

    Are you able to help? I can only find instructions for extracting a) after a single character (not a string) and no instructions for specifying the length of the string to be extracted.
    Thank you!!

    1. Hello!
      Please use the following formula -

      =MID(A1,SEARCH("irct",A1),19)

      The article above has all the information you need. I hope it’ll be helpful.

      1. Brilliant! Thank you so much for the quick response - much appreciated :-). One final question, if the cell doesn't contain "irct", what is the argument for returning a 0, rather than #VALUE!)

      2. Thank you for this!! I was looking for something similar.

  23. How Can I select sugar and flax from this formula

    "Milk, sugar, Vegetable shortening, canola oil, milk powder, cardamom essence, whole wheat flour, flax seed, raisin, sugar, almond, cashew"

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

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

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

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

  28. Hello!

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

    For example -
    Cell: ztt_tool_vacuum01_dev_rev_v3_icr_tt
    Extract: tool_vacuum01

    Another example -
    Cell: ztt_first_mom_hair01_col01_dev_rev_v9_icr_tt
    Extract: first_mom_hair01_col01

    Another example -
    Cell: ztt_mop_def01_col01_dev_rev_v4_icr_tt
    Extract: mop_def01_col01

    Could you help? Thank you :)

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

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

      Hope this is what you need.

      1. This worked wonders! Thank you kindly :)

  29. HI,
    Thanks a lot for your attention and reply,
    If you suggest different formulas for different patterns.
    I will be very thankful to you.

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

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

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

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

  31. superb explanation !...

    enjoyed throughout reading & understanding this article . maintain this easiness in every complex things. specially with illustration out of the box

  32. Thanks really it

    helpful for me

    keep going guys
    it
    use a smart functions
    thanks

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

  34. Hi,

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

  35. Hello,

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

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

    Many Thanks,

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

  36. Hi,

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

    Farms Limited #33-1A

    thanks,

    1. Hi!
      Try the following formula:

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

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

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

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

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

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

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

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

  38. I have a string

    Maintenance Charlie 1,2590 @ 2,000 service period 4/1/2021 to 3/31/2022

    I would like to extract from date in one column and end date in another column, help me how can I do that

  39. Hello,

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

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

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

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

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

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

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

    For instance

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

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

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

      I hope my advice will help you solve your task.

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

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

    Any tips?

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

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

  42. Hi ,

    When I generate a report daily one coloum consist of delimited text where I need to search some keyword like (count :1) I need to get the assigned value of count .. can some one help please

      1. Hi ,
        A sample sentence has been provided below , content will be be different need to get the value of count

        Eg :
        This has been updated count :02 , please check

        Excpeted output :02

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

    BIO_2021_02_23_Apple
    BRIEF_2021_09_28_Cat
    MR_TY_2020_03_20_Apple
    TAX_REC_2021_06_29_Bolton

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

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

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

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

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

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

      This should solve your task.

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

    output should be 3333

    1111-2222-3333-4444

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

    could you please check why the desire output not comming.

    Thanks a lot

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

    Only one digit will ever follow a letter

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

    I hope this makes sense

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

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

  47. Hi -

    I am trying to extract certain words from a lookup table. For example:

    Cell A1: Q1 Intelliquip invoice
    Cell A2: Q2 Intelliquip
    Cell A3: Intelliquip - RJE

    I am trying to look at cells A1:A3 and find the word "intelliquip", based on a full list elsewhere, and just return the word "Intelliquip". I've found that i can do this for A1: =IFERROR(RIGHT($A1,LEN($A1)-SEARCH($CA$2:$CA$188,$A1)+1),0), however, this would include everything after the world Intelliquip, when i just want that word.

    Thoughts on how to return just the specific word i'd like, when there is no structure in the cell i'm trying to find?

    Thanks!
    Logan

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

      =IF(ISNUMBER(SEARCH("Intelliquip",A1)),"Intelliquip","")

      1. Yes, that formula would be ok. However, I'm hoping for it to be multiple lookups other than just Intelliquip (other vendors, etc.). So that is why I was hoping to do a search(look up to a list of words I want). That way, I can just drag the formula down and it will output the one-word Vendor for me. So it would capture Intelliquip below, as well as Water Systems Council (just two examples) since that lookup table will have those words in there.

        Cell A1: Q1 Intelliquip invoice
        Cell A2: Q2 Intelliquip
        Cell A3: Intelliquip - RJE
        Cell A4: Water Systems Council invoice
        Cell A5: Q2 Water Systems Council
        Cell A5: Water Systems Council - RJE

  48. Hello,

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

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

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

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

  49. I need to retrieve the text with two conditions

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

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

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

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

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

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

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

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

      This should solve your task.

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