Comments on: Excel MID function to extract text from the middle of a string

MID is one of the Text functions that Microsoft Excel provides for manipulating text strings. At the most basic level, it is used to extract a substring from the middle of the text string. The tutorial explains the specificities of the Excel MID function and shows a few creative uses to accomplish challenging tasks. Continue reading →

Comments page 2. Total comments: 305

  1. Hi i have one query, I am unable to find the total months for the age 17:00 ,
    Can any one help me

  2. Hello, this thread has been very helpful. I have a column where wages are listed among other text. In some cells it is a static amount, in others it is a range. Example: "Regular Full-time, 40 hours/week $24.35 - $27.55 Hourly"
    I have used a LEN formula to figure out how many $ symbols there are in Column F and am using an if formula to return the wage if only one $ is present, but I am having trouble returning only the range. I do not want "Hourly". The ideal result would be "$24.35 - $27.55".
    Here is the formula I am using, but it only returns "$24.35".
    =IF(F14=1,TEXTBEFORE(TEXTAFTER(E14,"$")," "),TRIM(MID(SUBSTITUTE(E14," ",REPT(" ",99)),MAX(1,FIND("$",SUBSTITUTE(E14," ",REPT(" ",99)))-50),99)))

    1. Use substring functions to extract the desired result from the text. Try this formula:

      =MID(E14, SEARCH("$",E14), (SEARCH(" ",E14,SEARCH("#",SUBSTITUTE(E14,"$","#",2))))-SEARCH("$",E14))

      Hope this is what you need.

  3. 3459MohamedMO
    5648SalahSA
    5696YoussefYO
    8749RadwaRA
    4167MahmoudMA
    1236EmanEM

    i have a whole column like that and I want to extract the middle name out of the first 4 numbers and the last 2 letters
    how to get that? and how to use MID() in this case?
    the needed output:
    Mohamed
    Salah
    Youssef
    Radwa
    MAhmoud
    Eman

  4. Hi,

    I need to find the exact position of the cell A1= DBR from:
    cell A2 = DW=4000 DH=2100 TT=1 TTT=68 M=2 MDBR=0 EH=1 EC=1 DTR=0 DBR=50

    When I use =FIND(A1,A2) it will return me position of DBR from MDBR which is the first parameter containg DBR in the name, but after that we have DBR in the end which I am interested in.

    Which formula will be the best to extract exact name from A1.

    Thanks

  5. Good Evening, appreciate if anyone on the thread is able to assist.

    The following is an example of the raw data, Whereby OM = Original; CH = Chocolate; CS = Chia Seed; and the number preceding is the total quantity of product - if no number is stated, it is simply 1 X of the stated product.
    OMCHCS
    2OM2CH
    2CS2CS
    2CH1OM

    What I require is to split this data into columns (Name of Product followed by Quantity of Product).
    E.g. for the 1st line (OMCHCS):
    Cell A1: Original
    Cell A2: 1
    Cell B1: Chocolate
    Cell B2: 1
    Cell C1: Chia Seed
    Cell C2: 1

    E.g. for the 2nd line (2OM2CH)
    Cell A1: Original
    Cell A2: 2
    Cell B1: Chocolate
    Cell B2: 2
    Cell C1: Chia Seed
    Cell C2: 0

    Would this be possible using simply formulas in excel?
    Thank you.

    1. Hi! Your OMCHCS and 2OM2CH data are in different formats and cannot be split to cells using the same formula. For the text string OMCHCS:

      =MID(D1,1,2)
      =MID(D1,3,2)
      =MID(D1,5,2)

      To convert "OM" to "Original" you can use the IFS function or extract "Original" from the matching table using the VLOOKUP or INDEX MATCH formula. For example:

      =IFS(MID(D1,1,2)="OM","Original", MID(D1,1,2)="CH","Chocolate")

  6. Hi Sir,
    NEFT-HSBCN23212775696-NATIONAL INSURANCE CO LTD.
    How can I find out HSBCN23212775696 from this, which formula will I use?
    Please help.

    Thanks

    1. Hello! I’m not sure I got you right since the description you provided is not entirely clear. If you want to determine a partial match of text strings, use these instructions: How to find substring in Excel.
      If you want to extract the text, use this guide from the article above: How to get substring between 2 delimiters.

  7. sir i have doubt thats sa@07gar07 into sagar@0707 into dynamic whats the formula.

    1. Hi! Extract separate text strings using the MID function and combine them using the & operator. Please read the above article carefully.

      =MID(A1,1,2)&MID(A1,6,3)&MID(A1,3,3)&MID(A1,9,2)

  8. Hello, and thank you for your dedication, I have a cell with about 20 lines of data, from there I only want to extract 4 digits after the semicolon on the 10th line and nothing after. Data can look like; I get it to work with =TEXTAFTER(D4,Area Code: #") but i cannot get it to stop after those three digits and pull everything after..

    City: Miami
    State: FL
    Town: anything
    Area Code: # 305
    Province:
    Country: US
    Work Location: Local

    1. Hi! Your question does not match the formula and data you wrote below. If you want to extract the first 4 characters from the text, use the LEFT function. For example,

      =LEFT(TEXTAFTER(D4,"Area Code: #"),4)

      1. Thank you! that worked perfectly! i guess i was rushing trying to explain.

  9. Hello, I have a string like this

    SEP 28 SQUARESPACE INC. HTTPSSQUARES $46.61

    And I need to remove the date on the left and the price of transaction on the right. Is that something I can do in one formula?

  10. Hi,
    Im looking to extract the 7 digit number starting with 3 in a cell and place it in a cell on its own. The number changes in the string. It always starts with a 3 and has 7 digits.
    Below is an example:

    INV-8757 16/06/20 21591 1 x Carton (51 x 31 x 50cm @ 78Kg) PO 3115158 - 1 x Carton 1 x Valve
    INV-8757 16/06/20 21593 1 x Pallet (60 x 55 x 25cm @ 124KG)PO 3114189 - 1 x Pallet
    INV-8757 16/06/20 21594 1 x Box (20 x 15 x 25cm @ 1KG)PO 3115188 - delivery

    1. To find text based on a pattern, you can use regular expressions and the custom RegExpExtract function. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex). The formula might look like this:

      =RegExpExtract(A2, "3\d{6}", 1)

      I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression 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.

  11. Order No: Prices:
    AB124 3.20
    AB124 3.10
    AB124 3.87
    AB124 3.45
    CD245 3.20
    BQ458 3.10

    In the above scenario, How to pick only AB124's 2nd and 3rd value?

    1. Hi! If I understand your task correctly, pay attention to the following paragraph of the article above: MID formula to get the last name.
      It covers your case completely.

  12. I have this email script in my first colum A1:

    "We are glad to hear from you again.

    Please ensure that you account is being activated before the due date.

    Here is your user ID abcdefg123@popit.com"

    My question:
    Different customers would have different user ID
    How do I trim the login ID (abcdefg123@popit.com) to be appeared in another column : B1.

    Thanks :)

  13. Please support getting the text "RET-EXPIRED", "Expired", and "DAMAGES" only from the below text in excel.

    P-02-RET-EXPIRED
    P-07-Expired-2023
    P-07-DAMAGES-2023

  14. Hi Sir,

    FLAT/ROOM 2504, FLOOR 25, TONG BO HOUSE, FU TONG ESTATE, TONG CHONG
    FLAT/RM 18 5/F TSU YONG HSE TSU PONG NORTH ESTATE
    FLAT/RM 19 13/F MAI SHAK HOUSE SHAK MEN ESTATE
    FLAT/ROOM 2103, FLOOR 21, LENG CUN HOUSE, LENG KING ESTATE
    FLAT 05 15/F IN CHING HOUSE UN CHUNG ESTATE
    FLAT/RM 3106, 31/F, MAN KING HOUSE, TSZ MING ESTATE
    ROOM 2428, MIND SHIN HSE, MING SHING ESTATE
    FLAT 1019, FLOOR 10, MEN KONG HOUSE, MODEL ESTATE
    FLAT/ROOM 1106, 11/F YAN YUE HOUSE, TENG HANG ESTATE

    I want to find the starting position of XX HOUSE by looking two " " to the left of "HOUSE" in order to extract "X X HOUSE" from the string

      1. Thank you for you reply,
        However, I cannot find textsplit in my Excel 2016.

  15. I have a very descriptive Cell (2000+ in the column). I want to extract from each cell User: John Smith and user Certifcate Serial Number in two separate columns. How do I achieve this:

    The Key Distribution Center (KDC) encountered a user certificate that was valid but could not be mapped to a user in a secure way (such as via explicit mapping, key trust mapping, or a SID). The certificate also predated the user it mapped to, so it was rejected. See http s://go.microsoft.com/fwlink/?linkid=2189925 to learn more.

    User: John Smith
    Certificate Subject: @@@OID.0.9.2242.17200300.102.1.1=12008002283899 CN=JOHN SMITH (Affiliate), OU=Support Office, O=Microsoft, C=US
    Certificate Issuer: Digicert CA
    Certificate Serial Number: 6109AE1K
    Certificate Thumbprint: 75024E979EA9006B1770
    Certificate Issuance Time: 13113180000000
    Account Creation Time: 1332200000000

  16. Here the below two sentence given. first sentence has word OBI= so I used formula to extract details start from ORG= till OBI=
    The second sentence doesn't have word OBI= so please advise me how to use formula to take full details from ORG= if no OBI= word found.
    If OBI= found, then details should come till OBI= from ORG=
    if no OBI= not found then details should take all details from ORG=

    Sentence 1 - TRSF BOOK TRANSFER CREDIT SND=NOREF ORG=RSB IN OBI=ATTN/RSB
    Sentence 2 - TRSF BOOK TRANSFER CREDIT SND=NOREF ORG=CAIRE CONSULT IN

    The formula I used for sentence 1 is =MID(D1,FIND("ORG=",D1)+0,FIND("OBI=",D1)-FIND("ORG=",D1)+4)

    1. Hi! If I understand your question correctly, your formula can be used in the IFERROR function. If "ORG=" is not found and an error is received, all characters until the end of the text string are extracted.

      =IFERROR(MID(D1,FIND("ORG=",D1)+0,FIND("OBI=",D1)-FIND("ORG=",D1)+4), MID(D1,FIND("ORG=",D1)+0,100))

  17. Sir,
    As we know MID function returns a string value . without using VALUE function i got numerical output for =(MID(E5,1,1)+MID(E5,2,1)+MID(E5,3,1) this formula instead of concatenating . can you please explain how is that possible.

  18. Hi sir, Am searching for a formula. could you please help me finding it

    So I have the value, A2=True, B2=True, C2=True, D2 is True
    In E2 - if all the A2,B2,C2 & D2 is True, E2 should show True if incase C2 or anything is False, E2 should show False.

  19. Sun, 18 Jul, 2021, 10:38 pm
    Sat, 4 Sept, 2021, 11:53 am

    from the above i want to extract the month and year to the other column. Which formula will work for this?

  20. LISTER BANDAGE SCISSORS 14cm , RIGHT HAND (Reusable Scissors).

    From the above string i want to extract the size of the scissor (14cm). Which formula will work for it?

    1. Hi!
      If you have a common pattern in your data, you can use regular expressions and the custom RegExpExtract function. Here are detailed instructions and examples: How to extract substrings in Excel using regular expressions.
      Or split the text string into columns and use a fourth column. I recommend studying this article: Split string by delimiter or pattern, separate text and numbers.
      Also, to split a text string and take the fourth element, you can use the new CHOOSECOLS and TEXTSPLIT functions.

      =CHOOSECOLS(TEXTSPLIT(A1," "),4)

  21. Hi, I could not find the exact answer in my days finding, so trying to express below for a solution:
    I need to extract and build a BP Number (Business Partner in SAP) from certain GL listing Text field. The problem is different users write the description in Text varyingly. The formula that I have built is giving me partial result as I mention below, the Row-6 is not giving the right pick :
    Criteria:
    1. Find "BP" in the Text.
    2. Pick the number after BP up to the space.
    3. Count the length of the number.
    4. If the length is less than 10, then add 2000000000 to it, else if it is 10 then publish the same.

    =IF(OR(C3="11479996", C3="11479997"), IF(LEN(TRIM(MID(N3,FIND("BP ",N3)+2,10)))<10, 2000000000+TRIM(MID(N3,FIND("BP ",N3)+2,10)),"NA"),"NA")

    Text BP Number
    Rej PY 6500272182 BP 10176376 PYT#4014644196 2010176376
    BP#521636; PY#6500267772; PYT#4014644196 #VALUE!
    Rej 6500284071 Index 657510 #VALUE!
    REVERSAL 6500298182 Index# 794346 #VALUE!
    Rej PY 6500302221 BP 10239350 no USD corresp bank 2010239350
    Rej. PY 4015956978 BP 2010055678, accou. not exsit 2201005567

    Would be grateful for the guidance.

    Regards,
    Javed

    1. Hi!
      If I understand your task correctly, try the following formula:

      =IF(LEN((--MID(A1,SEARCH("BP",A1)+2,SEARCH(" ",A1,SEARCH("BP",A1)+3)-SEARCH("BP",A1)-1)))<10, (--MID(A1,SEARCH("BP",A1)+2,SEARCH(" ",A1,SEARCH("BP",A1)+3)-SEARCH("BP",A1)-1))+2000000000,(--MID(A1,SEARCH("BP",A1)+2, SEARCH(" ",A1,SEARCH("BP",A1)+3)-SEARCH("BP",A1)-1)))

  22. I am trying to validate and bring some specific text to a cell using find function based on a key text. Below example for your reference:
    Am-PE AN Type:Spouse;AM-P Count:Varies State;AM-P Design Cat:Hopefull, All Plats;OD_MR Call Value Instruct:Top Quiz
    I tried this but didn't work =MID(A2,SEARCH("AM-P Count:",A2)+1,SEARCH(";",AY2)-SEARCH("AM-P Count:",AY2)-1).
    I want to bring the value "Varies State" here. What I have suppose to do now

    1. Hi!
      If I understand the problem correctly, to extract the text string try this formula:

      =MID(A2,SEARCH("AM-P Count:",A2)+11, SEARCH(";",A2,(SEARCH("AM-P Count:",A2)+11)) - (SEARCH("AM-P Count:",A2)+11))

      1. It would be so helpful for me if you could help on the below too
        *If header B1- Type(Am-PE AN Type:)
        C1- Count(AM-P Count:)
        D1- Cat(AM-P Design Cat:)
        E1- Instruction(OD_MR Call Value Instruct:)

        *A2 has the combination of texts:- Am-PE AN Type:Spouse;AM-P Count:Varies State;AM-P Design Cat:Hopefull, All Plats;OD_MR Call Value Instruct:Top Quiz
        *B2 - I need to bring a set of text listed till ; from Column A2, which is if i need to bring "Spouse" in B2 what am I suppose to do,
        also same for
        C2 - "Varies States"
        D2 - "Hopefull,All Plats"
        E2 - "Top Quiz"

        Additionally, the texts in A2 will be mixed every time

          1. The issue is am bringing the A2 value using a vlookup function from another sheet, so what happens here is it sometimes bring too many text strings swapped like below examples

            Ex 1 - Fill with: Stories;Am-PE AN Type:Spouse;AM-P Count:Varies State;AM-P Design Cat:Hopefull, All Plats;OD_MR Call Value Instruct:Top Quiz;freq alert:2

            Ex 2 - Am-PE AN Type:Spouse;AM-P Count:Varies State;AM-P Design Cat:Hopefull, All Plats;OD_MR Call Value Instruct:Top Quiz

            So when use any formula with the key word "Am-PE AN Type:" in C2 cell it should only show the text string "Varies States" ending with ; and not the other texts like "Hopefull, All Plats" or "Top Quiz".

            Please help in resolving it

              1. Hi Sir, Sorry for coming back. Am facing another issue in validating the range of cells from A2 to C2.

                The issue is I have A2= True; B2=True & C2=True, Now I want to validate the cell D2 which should show "Good" if all the 3 three cells A2,B2 & C2 has the value True.

                And if any one of the cell(A2,B2 or C2) has False value the D2 should show Bad.

                It will so helpful if you could help me in it.

              3. Thank you so much Sir, it worked!!!

              4. Thank you so much sir. It helped a lot!

                I have another query and could you please help on this too

                Cell = A2 - Name
                B2 - Date
                C2 - Date
                D2 - formula cell

                I want to validate the cell B2 and C2 which has dates and to find both are matching, I used =B2=C2 in cell D2 and if B2 & C2 matches it shows True if not it shows False.

                And at the same time I want to use another formula in D2 to show the cell as Null if the A2 has no value. How do I show that? Anyhow B2 & C2 will have values only when A2 has value. so I need 2 formula's in D2. one is should should Null value when A2 is empty and True or false when B2 & C2 has value.

  23. Hello, i would like to provide the answer to question of Merc (february 14, 2023) about the way to extract the number/words with different length inside addresses.

    This the formula to put in the first cell beside the address:
    "=LEFT(A1;SEARCH(" ";A1)-1)"

    This is the formula to put in the second cell beside the address:
    "=MID($A1;COLUMN(B:B)+SUM(LEN($B1:B1));IFERROR(SEARCH(" ";$A1;COLUMN(B:B)+SUM(LEN($B1:B1)))-SUM(LEN($B1:B1))-COLUMN(B:B);SUM(LEN($B1:B1))+COLUMN(A:A)))" ==> you can slide toward right

    BR,
    Nicolò

  24. Hello! I'm hoping you can assist me with separating my address data, which I've provided examples of below. I would like to be able to separate the street number, street direction, and street name into separate columns. The issues I'm running into include different address lengths, some streets do not have a direction, and some street names are more multiple words. Thank you!

    5020 N HIGH ST
    5661 CRAWFORD DR
    1425 CHELMSFORD SQ S
    3535 W DUBLIN-GRANVILLE RD
    222 W NORTH BROADWAY

    1. Hello!
      Unfortunately, to use formulas or other tools to separate text by columns, all text strings must have the same pattern.

      1. Thank you for the quick response.

  25. What is the answer to this question?

    Cell A2 contains the US telephone number (939) 555-0113. What will be displayed using the formula =MID(A2,2,3)?

  26. Hi,

    Hi,
    I have cell contains 2871 characters including special characters as given below : I am looking for each colon items in separate cell. I do not want to use text to column function but want to use formula using MID, right substitute,LEN etc. Please help.
    Goods Description : VALVES, MANUAL 101-V3724 (PARTS OF MACHINERY)

    Party Ref. No. : SA-USIN-OA4035 KIND ATTN :
    Vessel : Invoice No. : PI10 - 2306675
    :
    POL : Invoice Dt. :
    ETD :
    Place of Supply :
    POD : Gross Weight(Kgs) : 7336.000 Our Ref. : CISF-XXXXXXXXXXX-1
    MBL/MAWB No. : XXXXXXXXXXXXXXXXX
    Net Weight : 0
    HBL/MAWB No. : No. Packages : 17
    Volume : 7.336 Consignee Name :
    IGM No. : 0 Shipper Name :
    Item No. : Comm. Invoice Nos : EN-21-0919INV022,EN-21-0921INV027,EN-21-0922INV02
    BOE No / Date : XXXXXX / 25-Oct-22 Total CIF Amt : 10982767.57 Total FOB Amt : 0.00

  27. I have a set of data in column, in each string there are numbers and one letter only.
    The letter can be in different position.
    I need a formula to extract that letter.
    Thanks

  28. I have a column from where I want to take out the grammage of that object. For example:

    25.5 GRM 1881 Preform
    2.74 GRM Closure
    1.75 GRM Closures
    10.46 GRM 29/25 Preform
    PRE 7.2 GM CLEAR 29/25
    PRE 7.2 GM BLUE 1 29/25 GC
    PRE 7.8GM CLEAR 29/25 GC
    PRE 7.8 GM BLUE 1 29/25 GC
    PRE 7.8 GM BLUE 6 29/25 GC
    19.0 GRM Preform

    So, here first 04 can be easily extracted with Text to column and using space as delimited. I am facing issue where the text starts from PRE and also at one instant the GM is written without space.
    I need a single formula to extract the grammage in numbers only that I can put in power query of excel table to work further.

    This is getting very confusing.

  29. Hi!

    Intel(R) Core(TM) i7-1065G7 CPU @ 1.30GHz (8 CPUs), ~1.5GHz

    How can I extract the "i7-1065G7"?

    TIA!

  30. i have received data in a timer format and the requirement is to get the first response time
    the data is in 16m,1h 31m,-194h 5m,-20m,3h, and the minus data represents the first response breached data and i have been using this formula which gave me negative value for few terms.
    =iferror(left(J2,find("h",J2)-1)*60,0)+iferror(mid(J2,iferror(find("h",J2)+1,1),find("m",J2)-iferror(find("h",J2),0)-1),0)

  31. Games Barney 20171027 $1250

    i want to extract date from above text. i have almost 500 row with same format but i couldn't figure out how to do this.
    pls help me

      1. Thanks for replying. I definitely use this tool. For now i have an assignment where i need to use mid and find formula to extract date from text.
        such as in this text : Games Barney 20171027 $1250
        i have to use Mid+find function to extract 20171027 from above text.

        i hope i am able to convey my message.

  32. How can I extract the characters between quotes when there are multiple instances of the delimiter and I am looking for a different result each successive column... IE:
    "Animal":"Dog","Color":"Brown","Location":"New York City","Date":"January 2022"
    the above text being in Cell A2, column headers B,C,D,E are Animal, Color, Location, Date respectively.
    The position of the given result and the length of each response varies. I have had some luck getting the response pulled in, but cannot figure out how to account for the appropriate length of each unique response so some are truncated and others are pulling in more characters than needed... ie Brown","Lo
    My brain hurts from trying to figure this out... PLEASE HELP!

    1. For reference, this is what I had so far:
      =MID(D2,FIND(H$1,$D2)+LEN(H1)+3,10), Where D2 is the source text and H1 is the category (column header)
      The nested FIND formula is to determine the position where I would find the desired result in the source text.
      Also, the $ are inserted because I intend to carry it over columns and rows.

      1. Another variation trying to use the delimiters, but then I get stuck trying to incorporate the number of characters:
        =MID(D2,FIND(H$1,$D2)+LEN(H1)+3,SEARCH("""",D2,SEARCH("""",D2)+1)-SEARCH("""",D2)-1)

        1. Hello!
          To extract all strings between delimiters from text, use a user-defined function RegExpExtract.
          You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex)
          Write the search pattern to a cell G2

          '"[^"]+"

          Use the formula:

          =SUBSTITUTE(RegExpExtract(A2, $G$2),"""","")

          To show the result by columns, use the TRANSPOSE function.

          =TRANSPOSE(SUBSTITUTE(RegExpExtract(A2, $G$2),"""",""))

          1. Didn't work... returning blank results. I set the match_case to False just in case.
            After a few more tweaks, I starting getting spill errors and it was pulling in the first couple characters into the first couple cells and then the remaining characters into a handful of cells below it.

  33. Hi
    It it possible to use the below formula to find multiple criteria? For example I am looking for letters beginning with GB and US but the current formula only returns those with GB.

    =TRIM(MID(SUBSTITUTE(E151," ",REPT(" ",99)),MAX(1,FIND("GB",SUBSTITUTE(E151," ",REPT(" ",99)))-50),99))

    1. Hello!
      The current formula cannot return "US". To do this, add the search "US". This can be done using the IFERROR function.

      =IFERROR(TRIM(MID(SUBSTITUTE(E151," ",REPT(" ",99)),MAX(1,FIND("GB",SUBSTITUTE(E151," ",REPT(" ",99)))-50),99)), TRIM(MID(SUBSTITUTE(E151," ",REPT(" ",99)),MAX(1,FIND("US",SUBSTITUTE(E151," ",REPT(" ",99)))-50),99)))

      I hope it’ll be helpful.

  34. This article gave me some perspective on how to build logic around these kinds of problems. With Google Sheets, you can just use the Split function and provide the delimiter and that does the job.

    1. Hello Daksh,

      there are actually a lot more ways in Google Sheets to extract data from different parts of cells. Feel free to look through this article to get to know them.

  35. A1 = Aaron (16 Years Old) [Gold Star]
    A2= Peter (16 Years Old) [Sliver Star]
    A3 = John (16 Years Old) [Bronze Star]

    Hi, If i have the above in in A1, A2 and A3,

    Can i use just one MID formula ti show
    Gold in B1,
    Silver in B2
    Bronze in B3

    Thanks in Advance for your help. i been trying and it is all error
    Eugene.

    1. Hello!
      To extract the word after the "[" character from the text, you can use the formula:

      =MID(A1,SEARCH("[",A1)+1, SEARCH(" ",A1,SEARCH("[",A1)+1)-SEARCH("[",A1)-1)

      Pay attention to the following paragraph of the article above - How to get substring between 2 delimiters.
      SEARCH function finds the characters "[" and " ".
      Then copy this formula down the column. You can also use autofill Excel formulas for this.

      1. Can you explain further the parts this formula please?

      2. Thank you Sir... Thank you i will have a look at the article again.

  36. HI
    with single statement can i pull the data.

    1) FGH-0622_Disb2of4 (need to pull Disb2of)

    2) DFG-0722_Disb20of40 (need to pull Disb20of)

    the formula is correct for 1st MID(A5,SEARCH("Disb*f",A5),7)

    1. Hello!
      You can't extract the second text using the MID and other substring functions because it has 8 characters instead of 7.
      To extract text between characters, I recommend using regular expressions. You can find the examples and detailed instructions here: Regex to extract strings in Excel (one or all matches).
      Here is an example of a regular expression for your task:

      =RegExpExtract(A1,"\Disb.*?\of")

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

  37. Hello

    Please advise me.

    As shown below,i want to extract the number before /CAP for the first one
    the second i want to extract the number before the /f.c

    is it possible or not, If it is possible could you please let me know.

    ROWACHOL 45/CAP 45 CAPS PKG
    DAPAVELDACTIN PLUS 5MG/1000MG 28/F.C.TAB 28 STP PKG

    Thanks in advance

  38. ctrl+shift+enter

    =TRIM(MID(SUBSTITUTE(CONCAT(IF(ISNUMBER(NUMBERVALUE(MID(SUBSTITUTE($A1,".",1),ROW(INDIRECT("1:"&LEN($A1))),1))),MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)," ")),". ",""),1,LEN($A1)))

    aaaa111.2222bbbbb3333.4444ccccggggcc5555.66666ggffff=TRIM(MID(SUBSTITUTE(CONCAT(IF(ISNUMBER(NUMBERVALUE(MID(SUBSTITUTE($A1,".",1),ROW(INDIRECT("1:"&LEN($A1))),1))),MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)," ")),". ",""),1,LEN($A1)))

  39. Dear Professor Alexander Trifuntov, it is a real pleasure to greet you.

    Regarding the extraction of text from a cell I have a couple of doubts. I am testing some codes in VBA to obtain the RGB color range that Excel brings by default. The function (UDF) gives me the result in a cell (e.g. C2) in the form RGB(255,255,255).

    I want to extract each of the corresponding values for each color in separate cells (e.g. D2:F2).

    I have already managed to obtain the value for the color Red (R) that goes in cell D2 as follows =NUMBERVALUE(MID(LEFT(C2,FIND(",",C2)-1),FIND("(",C2)+1,LEN(C2)))

    In the same way I have obtained the value for the color Green (G) that goes in cell E2 as follows =NUMBERVALUE(MID(C2,FIND(",",C2)+1,FIND(",",C2,FIND(",",C2)+1)-FIND(",",C2)-1))

    However, I have not been able to get a formula to obtain the last value corresponding to the color Blue that would go in cell F2, (this would be approximately the values that are further to the right of the last comma).

    Would you be so kind as to point me to a solution that would allow me to extract these last characters.
    I clarify that each one of the values changes on the scale from 0 to 255, according to the corresponding color.

    I appreciate your valuable help and attention to solve my doubt, thank you very much.

    1. Hello!
      Please try the following formulas:

      =MID(A2,FIND("(",A2)+1,FIND(",",A2)-FIND("(",A2)-1)
      =MID(A2,FIND(",",A2)+1,FIND(",",A2,FIND(",",A2)+1)-FIND(",",A2)-1)
      =MID(A2,FIND(",",A2,FIND(",",A2)+1)+1,FIND(")",A2)-FIND(",",A2,FIND(",",A2)+1)-1)

      I hope this will help

      1. Dear Professor, thank you very much for your valuable collaboration, indeed, all the formulas work correctly, I appreciate your kind attention!

  40. Hello,

    Here is the string that I have - 'FINISHED GOODS:Ballistic Inserts:400 Series:4400:4400-SA-MC-M'

    I'd like to extract '4400-SA-MC-M' from the end of it. I have a range of products that I'd like to extract that last string from but the string length (meaning number of characters in it) is not always the same (some part numbers are longer by one or two characters).

    It would also be helpful to be able to extract the '400 series' and the '4400' separately.

    Any help you could provide would be awesome.

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

      =MID(A2,SEARCH("#",SUBSTITUTE(A2,":","#",LEN(A2)-LEN(SUBSTITUTE(A2,":",""))))+1,50)

  41. 2-TMP-001B-212-AM-00006-01

    I WANT TO EXTRACT 212-AM-00006
    AS A RESULT, WHAT FORMULA SHOULD I HAVE APPLIED.

      1. Yes i understood your concern, but it has to be done more times again and again, could you tell me the formula.

        1. Hi!
          It is impossible to accomplish your task with one formula. Check out the linking guides I recommended and follow the steps.

    2. =MID(A25,FIND("-",A25,FIND("-",A25,FIND("-",A25)+1)+1)+1,FIND("-",A25,FIND("-",A25,FIND("-",A25,FIND("-",A25,FIND("-",A25,FIND("-",A25)+1)+6)-FIND("-",A25,FIND("-",A25,FIND("-",A25)+2))))))

      i used this formula, but the result is 212-DG-0000

      Missing "5" at last, Result Must be 212-DG-00005

  42. I have on A1 and A2: In B1 and B2 I write: Resoult:
    A1: HK$11.720 +A2*3 #VALUE!
    A1: HK$0.127 +A2*3 #VALUE!

    If I use:
    For A1 (HK$11.720) =(RIGHT(A2;LEN(A2)-FIND("$";A2)))/1000 11,720 (as normal number) or: =(RIGHT(A2;LEN(A2)-FIND("H";A2)-FIND("K";A2)))/1000 11,720 (as normal number)

    but if use the same formula for A2

    For A2 (HK$0.127) =(RIGHT(A2;LEN(A2)-FIND("$";A2)))/1000 #VALUE! or: =(RIGHT(A2;LEN(A2)-FIND("H";A2)-FIND("K";A2)))/1000 #VALUE!

    If I use (to add –FIND(“$”;A2):

    On A2 (HK$0.12) =(RIGHT(A2;LEN(A2)-FIND(“H”;A2)-FIND("K";A2)-FIND("$";A2)))/1000 0,027 (as normal number but, missing the “1”)

    If I use:

    On A2 (HK$0.12) =(RIGHT(A2;LEN(A2)-FIND("K";A2)-FIND("$";A2)))/1000 0,127 (as normal number)

    Which formula can be use for the two quantities in one time?

    Thanks

    1. Sorry and thanks for your help, I try to clarify as:

      I have:
      A1.- HK$11.720 Write in B1.- +A1*3 Resoult: #VALUE!
      A2.- HK$0.127 Write in B2.- +A2*3 Resoult: #VALUE!

      If I use the formula on:
      B1.- =(RIGHT(A2;LEN(A2)-FIND("$";A2)))/1000 《or》
      =(RIGHT(A2;LEN(A2)-FIND("H";A2)-FIND("K";A2)))/1000 Resoult: 11,720 (as normal number can use)

      But (with the same formula for HK$0.127 (decimal)), can not be use!
      B2.- =(RIGHT(A2;LEN(A2)-FIND("$";A2)))/1000 《or》
      =(RIGHT(A2;LEN(A2)-FIND("H";A2)-FIND("K";A2)))/1000 Resoult: #VALUE!

      If I use to add in B2: –FIND(“$”;A2):
      B2.- =(RIGHT(A2;LEN(A2)-FIND(“H”;A2)-FIND("K";A2)-FIND("$";A2)))/1000 Resoult: 0,027 (as normal number but, missing the “1”)
      If I cancel to use in B2: –FIND(“H”;A2) and add -FIND("$";A2)
      B2.- =(RIGHT(A2;LEN(A2)-FIND("K";A2)-FIND("$";A2)))/1000 Resoult: 0,127 (as normal number can use)

      NOTE:
      A1.- HK$11.720 should to be 11,720
      A2.- HK$0.127 should to be 0,127

      Which formula can be to use for the two quantities HK$11.720 to be 11,720 and HK$0.127 to be 0,127 insted 2 formulas, one for natural number and one for decimal number?

      I need to create an excel table with more than 2.500 datas/x column take (page/copy) from a Government HK web for financial companies in HK.

      When I make the page/copy, the HK$(+number) are no numeric and also I don´t know, ” its not as texto”, can not use on formulas, can not change from texto to number.

      I can do/use it if I change/create as normal number without HK$ and with “,” instead “.”, by create a formula as explain above.

      Thanks Alexander

      1. A1.- HK$11.720
        A2.- HK$0.127
        A3.- HK$0.048

        Formula in B1 / B2 / B3: =(RIGHT(A2;LEN(A2)-FIND("$";A2)))/1000 or =(RIGHT(A2;LEN(A2)-FIND("H";A2)-FIND("K";A2)))/1000

        Resoult:
        B1.- 11,720 (as normal number)
        B2.- #VALUE!
        B3.- #VALUE!

        Formula in B1 / B2 / B3: =(RIGHT(A2;LEN(A2)-FIND(“H”;A2)-FIND("K";A2)-FIND("$";A2)))/1000

        Resoult:
        B1.- 0,900 (as normal number but, missing the “11”)
        B2.- 0,027 (as normal number but, missing the “1”)
        B3.- 0,048 (as normal number)

        Formula in B1 / B2 / B3: =(RIGHT(A2;LEN(A2)-FIND("K";A2)-FIND("$";A2)))/1000

        Resoult:
        B1.- #VALUE!
        B2.- 0,127 (as normal number )
        B3.- 0,048 (as normal number)

        1. Hi!
          To extract a number from the end of a text try this formula:

          =--SUBSTITUTE(MID(A2,SEARCH("$",A2)+1,50),".",",")

      2. Hello!
        We have a ready-made solution for your task. I'd recommend you to have a look at our Remove Characters tool.
        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.
        You can replace "." with "," using the standard Find & Replace tool.
        If something is still unclear, please feel free to ask.

  43. Hi, if I have a whole number like 20090206 and I want to format into dd:hh:mmss (20:09:02:06). How would I proceed to include the ":" delimiter spaced by two numbers? Thank you!

    1. Hi!
      With the MID function, take every 2 digits from the number.

      =MID(A1,1,2)&":"&MID(A1,3,2)&":"&MID(A1,5,2)&":"&MID(A1,7,2)

      The formula returns text.
      You can use a custom number format:

      ##":"##":"##":"##

  44. In a single cell I have a cluster of numbers separated by tabs or spaces ; I am trying to isolate just the starts with 79 numbers that are always 13 digits in a separate cell
    Example...
    7981107135154
    110567815X
    1316135086

    I was using formula =TRIM(LEFT(SUBSTITUTE(MID(C5,FIND("7",C5),LEN(C5)),"",REPT("",LEN(C5))),LEN(C5))) but it doesn't put a stopper to the digits so I get everything that follows it.

    Any Ideas how I can isolate it?
    Thanks
    NEMA

    1. Hi!
      Sorry, it's not quite clear what you are trying to achieve. Explain in more detail. Give an example of your cluster of numbers and the desired result.

  45. Thank you for the formula. It works.

  46. Hi,

    I am trying to convert 30-jun-21 01.45.18.488173000 pm to 1:45. Please help someone with mid and substitute function together.

    1. Hello!
      Please try the following formula:

      =TIME(MID(A2,SEARCH(" ",A2,1)+1,2),MID(A2,SEARCH(" ",A2,1)+4,2),0)

      1. Hi,

        I am trying with the formula TIME(MID(A2,SEARCH(“ “,A2,1)+1,2),MID(A2,SEARCH(“ “,A2,1)+4,2),0).

        The timestamp is showing as 12:07:00 PM.
        I am not getting the “ss” number as result.
        It should be 12:07:24.

        Please guide.

        1. Hi. I was applying the formula in a rough data timestamps. When I apply it on real data sheet timestamp. The file is pulled from a sql, it is not capturing the ‘ss’ value.

      2. Hi. The above formula works to convert into normal time format.

        The above timestamp format is 30-jun-21 01.45.18.488173000pm which is 1:45:00.

        I have got four timestamps:
        17:39:38
        17:41:35
        17:45:35
        17:45:00( convention of above mentioned timestamp 1:45:00 +TIME(4,0,0)

        I am trying to get the time difference with consecutive timestamps which I will get 3 time difference.

        Problem: I have applied ABS(timestamp1-timestamp2) to find each difference.

        But when I apply, =MAX(timedifference1,timedifference2, timedifference3)

        I get the time difference of last two timestamps which is of 35 secs as maximum value. And I tried to convert all to text that shows the last timestamp is taking both date and time in calculation.

        And other two difference are 00:01:57 and 00:04:00.

        I should get 00:04:00 as maximum value. Please help me out on this as I am trying with random formulas but no success.

      3. Can I get the time in 1:45 PM? The formula works but it shows 1:45 AM.
        Please advise on how timestamp can be shown in both AM and PM when pulled the respective data in AM and PM.

  47. This is awesome! I am managing an excel sheet of my expenses and I just copy pasted your formula =MID(A2,SEARCH(", ",A2)+1,SEARCH(", ",A2,SEARCH(", ",A2)+1)-SEARCH(", ",A2)-1)

    It was so accurate. Thank you for sharing this! Amazing!

  48. caution, this webeditor corrupts the formulas when posting. I did type a completely different formula than the one stated in my comment above!!!!! don't use it!

  49. the string published above for selecting a string of letters between certain keys in that string is unfortunately wrong. The function MID requires more brackets, and uses semicolon.
    Use this one if you want to select the string between "". E.g. in field A2 you have the string: improfessionalexcel, which will give you the word "using".

    =MID(A2;SEARCH("";A2)-(SEARCH("<";A2)+1)))

    1. Hi!
      The formula is correct and works. The use of semicolons depends on the regional settings of your Windows.

  50. Hello! I hope you guys can help me with my case :)

    %B6009210202107390^SMITH/JOHN I ^4912120000004825853 000 ?;6009210202107390=49121200000000000000?

    If I would want to extract "^4912120000004825853" how should I use this formula?

    Thanks!

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

      =MID(A2,SEARCH(" ^",A2,1)+2, SEARCH(" ",A2,SEARCH(" ^",A2,1)+1) - (SEARCH(" ^",A2,1))-2)

      Hope this is what you need.

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