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

  1. this function =REPT("0",4-LEN(A1))&A1 use for Len sent
    Result
    1
    0001
    But how to use for 1 to extend 1000.

    1. Hello Vivek!
      If you want to add 0 at the end of the value in cell A1, please use this formula:
      =A1 & REPT("0",4-LEN(A1))
      If you want to increase the length of a string, please change number “4” in the formula to the necessary number, e.g. 5,6,7, etc.
      If you mean something different, then please describe your problem in more detail.

  2. Apple iPhone 6 Space Grey 16 GB RAM ROM
    Model Name
    Model
    Ram
    Rom
    Alag kesa kr sakte h

  3. I am using the MID function to concatenate three columns. Similar to this formula
    =MID(A1,6,1)&MID(A1,8,1)&MID(A1,9,6).
    The formula works, but there is a space between the concatenations. How do i remove these spaces?

    1. Hello,
      Hello Gregg!
      The formula below would help you get rid of the spaces in your text:
      =SUBSTITUTE(A2," ","")

      There is also a ready-made solution for your task called Remove Characters tool that will delete the necessary characer in tyour range in a click. Check out the tool's manual here

  4. Hi.
    I have a rather complex formula and one portion of it is not working. Basically, one cell (C17) Has wording that shows how many boxes in a bundle there is. However, this item has 2 parts to the box. The cell says "375 TOP450 BOTTOM" which represents that the top portion of the box comes 375 in a bundle and the bottom portion comes 450 in a bundle. It uses the Char(10) function to place it in 2 lines within the same box. In another box (B17) is the amount of cases that are needed for a run. What I need to return in a separate box is a calculation of how many bundles are needed for the top and bottom rounded up to the nearest number. Essentially, if we need 1875 cases total it should return "5 TOP 5 BOTTOM". The first half of the formula works fine. The second half is not recognizing the extracted number as a number which is making it return a VALUE# error. I tried to wrap the function as a number but that still down't work.
    Here is the part of the formula that works and returns 450 as expected:
    =MID(C17,SEARCH("P",C17)+2,ABS((SEARCH("P",C17,SEARCH("P",C17)-1)-SEARCH("B",C17)+2)))
    It is when I try to divide the total cases needed by that number extracted extracted that I get the error.
    1875/(MID(C17,SEARCH("P",C17)+2,ABS((SEARCH("P",C17,SEARCH("P",C17)-1)-SEARCH("B",C17)+2)))) should return 4.16
    If I add the Value function like below, I get a value error.
    1875/value(MID(C17,SEARCH("P",C17)+2,ABS((SEARCH("P",C17,SEARCH("P",C17)-1)-SEARCH("B",C17)+2))))
    How can I get the mid function depicted to recognize the return as a number?

    To be clear, the first portion of my formula worked. So I was able to extract the 375 and divide 1875 by 375 and return the expected value as 5. It's only this second half I am having trouble with. Can anyone please help?

  5. =IF(MID(J2,2,1)="O","OO","RT") --- i have this formula correctly.

    However, i need to add another criteria MID(J2,3,1)="O"
    Please help.

    Thank you!

  6. Great article!
    I'm trying to do a complex string and struggling to get the final output:
    Original=C:\zeddn\AI01653_-W_DER8ZZ13.pdf
    Formula Output= \AI01653_-W (close - but I don't want the \ in the result.
    '=MID(A30,SEARCH("\",A30,SEARCH("\",A30)+1),SEARCH("_",A30,SEARCH("_",A30)+1)-SEARCH("\",A30,SEARCH("\",A30)+1))

    Using your example with a -1 at the end, doesn't work either:
    Original=C:\zeddn\AI01653_-W_DER8ZZ13.pdf
    Formula Output= \AI01653_-W_DER8Z (not close, do not want anything from the second underscore on in the result.
    '=MID(A30,SEARCH("\",A30,SEARCH("\",A30)+1),SEARCH("_",A30,SEARCH("_",A30)+1)-SEARCH("\",A30,SEARCH("\",A30)-1))

  7. Hi I have a query, i have to search multiple 8-10 Character strings from one cell example A1 like SAM1, SAM2, SAM3, SAM4, ZOP1, ZOP2, ADS1, ADS2.... and need to update the matching value example B2 SAM12345. Kindly advise me how could this be achieved. I have used the below formula but it has not worked, kindly provide your assistance....
    IF(ISNUMBER(SEARCH({"SAM0","SAM1","SAM2","SAM3","SAM4","SAM5","SAM6","SAM7","SAM8","SAM9"},$A1,8))=TRUE,MID($A1,SEARCH({"SAM0","SAM1","SAM2","SAM3","SAM4","SAM5","SAM6","SAM7","SAM8","SAM9"},$A1)+9,7)

  8. Hi,
    i want to "HSBC - Any domestic business banking relationships - Please now think about your personal banking relationships and the business' domestic banking relationships in Australia" this text as "Please now think about your personal banking relationships and the business' domestic banking relationships in Australia - Any domestic business banking relationships - HSBC", please help me how i can do this.

  9. Hi!
    I am using the following to extract "ES" text from a cell. How can I edit the formula to extract additional text, i.e. "ES", "MS", and "HS"? Thanks a lot in advance!
    =TRIM(MID(SUBSTITUTE(F19," ",REPT(" ",99)),MAX(1,FIND("ES",SUBSTITUTE(F19," ",REPT(" ",99)))-50),99))

  10. Can i make mid function start from the right direction and select the number on the left side of the spesfic number with out use right function ?

  11. HI, I used this formula =TRIM(MID(SUBSTITUTE(E4991," ",REPT(" ",99)),MAX(1,FIND("IL",SUBSTITUTE(E4991," ",REPT(" ",99)))-50),99)), which worked well with my case 2 and 3 below but did not give the desired result for case 1 and 4 due to the fact that there are no space in the case 1.
    Result i need is 16 characters starting with IL included.
    Case 1 : MF20020011111IL11000NG0000001
    case 2 : MF20020022222 IL11000NG0000002 revalued
    case 3 : IL11000NG0000002 MF20020022222 revalued
    Case 4 : MF20020033333IL11000NG0000003revalued
    Thanks

  12. trying to get it to use an IF function along with a MID in extracting numbers withing a string of both text and numbers.

    PBQ4X5 - using =MID(A2, SEARCH("Q",A2)+1, 1) to return the "4" and the same with "X" to return the number associated with it. Unfortunately when it runs across a sequence that doesn't include either the X or the Q (PBI5G3), I get "#VALUE!". I also get "#VALUE when trying to "sum" the number columns that the formula returns. Trying to come up with an IF formula to incorporate with the MID and SEARCH so that it will return a "0" when it can't find the particular text (Q or X). Any help would be appreciated, I've been working on this for quite a while.

  13. Hi, Fantastic Article!
    Is there any way to search for additional data within the same cell and have all the data that is pulled out, separated by a comma?
    I have a cell with 1 to 5 user names and ID's. The ID's are in parenthesis and I want to pull the ID's out to be separated by a comma.
    My worksheet is setup like the data below in a single column, but with 240 rows.
    Smith,Jim A (123456)Doe,John (789123)
    Doe,Jane(393027)
    Boss,Tim (293029)Deer,Fred(001289)Stern,Greg(148900)
    I'm trying to get the data to return like this:
    123456,789123
    393027
    293029,001289,148900

  14. Hello,
    I am looking to rearrange a date using Left, Mid and Right; unless someone has a better way to do this. Mt date is for example 20190101 ... I want to reformat the call to look like 01/01/2019. I am trying =RIGHT(A1,2),MID(A1,4,2),LEFT(A1,4)

    1. I don't know how to delete my comment but as I was about to give up I figured it out!
      =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)
      20190101 changes to 01/01/2019

  15. Hi there, I need help.
    Lets say I have a series starting with A until V
    Lets say I have a sequence that reads "ABCDEFGHIJK", now I need something that can say TRUE when my series contains more than 7 letters from the group A to K, and false when my series contains less than 7 letters from the group A to K.

    For example.
    ABCDEFGHIJK = TRUE
    ABCDEFGLMNO = FALSE
    ABCDELMNOPQ = FALSE
    ABCDEFGHRST = TRUE

    Any help would be greatly appreciated. :)

  16. HI I AM RAVI ,
    I NEED ONLY 5 WORDS IN THIS FORMAT,CAN ANY ONE HELP ME TO SUPPURATE IN FORMALS BASE,
    S5235/ACHYUTH

    1. Dear Mr Ravi
      Please use this formula to find the 5 charecter in specified cell.

      =MID(A1,FIND("S",A1,1),5)
      Note : A1 is the example cell reference for your requirement.

  17. how to extract only the number say suppose there are 100 of rows.. in one row it will be abcd - 2571818 & in second row abcd (173897).
    please help

  18. Hi,
    So I've downloaded your add in for excel, text toolkit, did what i had to do and was done in half an hour.

    Thank you! One qestion, is this add in in excel 365 online free?

  19. I tryed different things, gor to that function to and it worked until I came across a cell with 6 digit nummer starting with 3XX XXX. I wish I could find VBA code like the one for the extracting e-mail from string, it saved me days of work, unlike this phone number problem.

    Function ExtractEmailFun(extractStr As String) As String
    'Update 20130829
    Dim CharList As String
    On Error Resume Next
    CheckStr = "[A-Za-z0-9._-]"
    OutStr = ""
    Index = 1
    Do While True
    Index1 = VBA.InStr(Index, extractStr, "@")
    getStr = ""
    If Index1 > 0 Then
    For p = Index1 - 1 To 1 Step -1
    If Mid(extractStr, p, 1) Like CheckStr Then
    getStr = Mid(extractStr, p, 1) & getStr
    Else
    Exit For
    End If
    Next
    getStr = getStr & "@"
    For p = Index1 + 1 To Len(extractStr)
    If Mid(extractStr, p, 1) Like CheckStr Then
    getStr = getStr & Mid(extractStr, p, 1)
    Else
    Exit For
    End If
    Next
    Index = Index1 + 1
    If OutStr = "" Then
    OutStr = getStr
    Else
    OutStr = OutStr & Chr(10) & getStr
    End If
    Else
    Exit Do
    End If
    Loop
    ExtractEmailFun = OutStr
    End Function

    1. Hello, Kata,

      We are always ready to help you, but we do not cover the programming area (VBA-related questions).
      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com

      I wish I could assist you better.

  20. Hi, I have this formula: =MID(F3;FIND("09";F3;1);14)

    to exract phone number from text string, but it shows #VALUE! when there is no number in the string. I htere a way to insert this funtion into some other to get just an empty cell?

    I'm no excel expert so any help is welcome.

    THX

  21. Hi Anna,

    I'm trying to use MID within a SUMPRODUCTS function, but I want to compare the result to a number value, not a string. I tried wrapping the MID array in a VALUE function, but it returns an error. Do you know if there's any way around this?

    Essentially my code looks like this:
    SUMPRODUCT(A1:A100,--VALUE(MID(B1:B100,3,2))<18)

    Thanks

  22. Very useful at work! Thank you very much for making this article. Much appreciated!

  23. I created a Mid formula to pull out a letter of a number sequence, so I could determine an agency (18N12345 = mid formula pulls out the "N"). However, when I have 18F12345, the F turns red, and the cell color turns pink. The first few times I did it, it crashed excel. Any idea why the "F" has that effect? I have no conditional formats created for these cells.

  24. PO=65777570000 ASN=7266271

    I need to extract the mid value of the above line that is "65777570000"

    1. Guna:
      A formula to extract 65777570000 uses the MID function.
      Using your sample where it is in K6 the formula is:
      =MID(K6,4,11) It says, "In K6 go four characters from the left and extract the next 11 characters."

  25. I have a worksheet with text in column A. I need to find the 2nd time a phrase occurs and extract the number from the right of the common word.
    I can extract the entire line, but need the 2 characters from the second occurrence. Can't figure out how to do this.

  26. HI,

    Please advise me.

    As shown below,i want to divide only regional name (mark in ===) in excel sheet.
    is it possible or not, If it is possible could you please let me know.

    859 - KANINS - KANINS JAKARTA 2 - KANINS JAKARTA 2
    ===============

    Thank You

  27. Hai,
    This is beyond from the subject.
    I wants to know how to include (value) for each records which are indicating with minus figures. Eg -50 = (50).

  28. What If I Have Like A $ Sign Followed By Space Then The Number I Want ? Can U Help Me ?

  29. Thank you Svetlana for the great article! It's awesome!

    I would really appreciate some help, I have been going crazy trying to find the solution. Could someone please help me with a formula to extract the name, Barack Obama from the string below?

    [{"account_id":555,"name":"Barack Obama "}]

    Thank you very much!

    1. My answer:

      I placed your code in cell C4 then added:
      =IF(ISNUMBER(SEARCH(E4,C4)),"Found - "&E4,"* No Match *")

      to cell D4 and in cell E4 I added the text "Barack Obama"

      The formula will look in cell C4 for whatever text you have in cell E4 and if it is found, the cell D4 will reflect what was searched or it will return
      * No Match *
      A nice feature of SEARCH is that it is not case sensitive.

  30. please help me.
    want to extract text and numbers from one cell to different rows with function.
    each of the following line should be extracted to different rows.
    following is in only one cell.
    vb61-152262
    fg2004-229550
    ert2005-065548
    df2010-104283
    we63-313541
    wer2009-100693
    r2002-017302
    as07-008164

    1. Hello,

      If I understand your task correctly, please try the following formulas:

      1. To extract text:
      =SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1),"")

      2. To extract numbers:
      =RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)

      Hope this will help you with your task.

      1. Hi, can you please explain how the A1&"0123456789" this part in the formula

  31. I am trying to use the Mid/Find function on this problem but I can't seem to get it correctly.

    SNACKNATION 310-845-7744 CA
    CANDLEWOOD SUITES ST SOUTHFIELD MI
    SUBURBAN IMPORTS OF FARMINGTN HLS MI
    EGENFEE* 866-397-2677 WA
    KROGER #710 00000071 HARPER WOODS MI

    I need to separate these on each column but I'm always getting the #VALUE :(

    can someone please help me?

  32. Hi! Thank you for this very informative article.

    Still i have some problems I wonder if you can help me out with

    I have this string and many other similar to this:
    "1000Miglia 047 7,5x17 5-108 ET45 CB63,4"
    And I want to extract the numbers infront of "x" from the second " ".
    That means in this text i want to extract the number "7,5"

    Is there any simple formula for that?

  33. Hi Svetlana,
    This is exactly what I looking for. In the TRIM formula where a dollar amount was extracted, I replaced the "$" with two characters "V-" and no matter the length of the work (ie: V-1234 or V-1234A), the correct information was extracted. How would you build this in an IF function where the characters being searched vary. Example: T-1234, P-1234, V-1234, C-1234, D-1234, E-1234; IF "T-", IF "P-", IF "V-", so forth.
    Thank you for the formula, I just need to build on it. Any help would be greatly appreciated.

    1. Hi, Shelia,

      would it be possible for you to send us your workbook with the source data and the result you expect to get to support@ablebits.com? Our technical specialist would take a look into your task to advise you better.
      Don't forget to include the link to this comment to your email.

      Thanks!

  34. Hi Svetlana!
    Nice info! I have a question. I try to extract some characters from a cell twice. Let me explain:
    I have some words I need to eliminate from the original cell. Example:
    "My Dog is a beatiful black animal" to become "My Dog is a black animal".
    Is there a way to apply the MID function twice in the same cell who let me extract "My Dog is a" and then "black animal"? And if is not possible... Is there a way to do?
    Thanks a lot!!!

  35. HI,

    Please advise me.

    As shown below,i want to divide only period in excel sheet.
    is it possible or not, If it is possible could you please let me know.

    AKASH ALLAMUNENI 05/01/17-05/31/17 176 HRS 73

  36. Hi,

    So I have used the MID function for various financial data, but when the data has 1 or 2 zeros as the first decimals, it gives faulty readings (4..2 and "VALUE!" respectively). I assume this has something to do with "NUM_CHARS" and the fact that excel does not recognize a zero at the end. When I use this function: "=MID(40.20,1,LEN(40.20)-3)&"."&MID(40.20,LEN(40.20)-1,2)&"%"" it gives "4..2" rather than "40.20%".

    I tried to change the "40.20" to custom rather than "number" but it wouldn't stick. When I went back into format cells, it was back on "number".

    If there is a brilliant geek somewhere out there, I would appreciate it sooo much

  37. Awesome thanks a lot..very helpful article.

  38. thanks a lot.very very good

  39. =1+LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

  40. Hi Svetlana.
    I have a column for dates and I want to count how many dates are there? if there are more than one date in a single cell, separated by a comma
    (eg.in cell A2=08/02/17,09/02/17,10/02/17). it should come 3.how can i count it using 'countif'formula?
    I tried
    =countif (a2:a10,"*"&",")
    but it doesn't work.
    can u help me on this?

    1. =1+LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

  41. Great article. Find your sight so helpful.
    I'm trying to pull specific digits from a string - WC5-39S-311704-022
    I need 6th, 8th & 9-14th, so 9-311704.
    Trying for a few days to figure this out.
    Any help is appreciated.

    1. Hi Chuck,

      You can concatenate 3 Mid functions, like this:
      =MID(A1,6,1)&MID(A1,8,1)&MID(A1,9,6)

      1. 2020;01;10;00;00;21.08;74.00;1013.70;27.00
        The question is how can I get the values after each ";" all in different columns

        1. Use Excel Text to Columns feature with ; as the delimiter

      2. Thank you so much svetlana cheusheva, I am tried to find the above formula tip from many URL's, finally I got it. Thanks my solution solve now! Great help

  42. wOW Svetlana!!!! Very Nice Article.

    1. Thank you Rohan! I am glad you've found it helpful :)

      1. You are genius!

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