Excel: Extract text with FIND and MID

Data analysis begins with usable data, and that means arranging every item in a data set into its own field where we can count, sort and otherwise test it out.

But what if you get a spreadsheet where the pieces of data are all packed in one field? Say, something like this (which I cobbled together from Major League Baseball data in honor of the Nationals’ first playoff appearance):

NAME: Sean Burnett POS: RP AGE: 30 WT: 200 BORN: Dunedin, FL SALARY: 2350000
NAME: Tyler Clippard POS: RP AGE: 27 WT: 200 BORN: Lexington, KY SALARY: 1650000
NAME: Ross Detwiler POS: SP AGE: 26 WT: 174 BORN: St. Louis, MO SALARY: 485000
NAME: Christian Garcia POS: RP AGE: 27 WT: 215 BORN: Miami, FL SALARY: N/A
NAME: Gio Gonzalez POS: SP AGE: 27 WT: 205 BORN: Hialeah, FL SALARY: 3335000
NAME: Mike Gonzalez POS: RP AGE: 34 WT: 215 BORN: Robstown, TX SALARY: N/A
NAME: Ryan Mattheus POS: RP AGE: 28 WT: 215 BORN: Sacramento, CA SALARY: 481000
NAME: Craig Stammen POS: RP AGE: 28 WT: 200 BORN: Coldwater, OH SALARY: 485000
NAME: Drew Storen POS: RP AGE: 25 WT: 180 BORN: Indianapolis, IN SALARY: 498750
NAME: Jordan Zimmermann POS: SP AGE: 26 WT: 218 BORN: Auburndale, WI SALARY: 2300000

Let’s say you want to extract the city of each player’s birth into a separate column. The varying length of each player’s name means the birth place isn’t always in the same position in the string, so a typical text-to-columns operation won’t work. So, how to do it?

The answer lies in two very handy Excel functions: FIND and MID.

FIND locates characters you specify and returns its numeric place in the string.

MID returns X characters from a string beginning at a location X you specify.

For example, we can locate the position where each city name begins by using FIND to locate the string “BORN:” in each cell. The city name itself always starts six characters after the position of that string, so we add six to the result:


In the first row above, the functions returns 50. In the second row, 52. We’ll feed that value to the MID function as the starting point for our extraction.

MID takes three arguments: Text or cell, position to start, number of characters to return. So, we use the above FIND function as the second argument and, for now, extract 10 characters:


That gets us part of the way there. We’re starting in the right spot, but 10 characters isn’t always the length of the city and state, so it leads to choppy results:

Dunedin, F
St. Louis,
Miami, FL 
Hialeah, F

What we need to do is tell MID the exact number of characters to return each time even though the length of the city varies. We can figure this out using FIND again.

The city name is always followed by the word “SALARY”. So, if we search for the position of that word and subtract the position of “BORN,” we’ll get the length of what’s between the two. The ultimate formula looks like spaghetti but works just fine:


Used on the example text, it returns:

Dunedin, FL 
Lexington, KY 
St. Louis, MO 
Miami, FL 
Hialeah, FL 
Robstown, TX 
Sacramento, CA 
Coldwater, OH 
Indianapolis, IN 
Auburndale, WI

That’s it. Fairly handy and further proof that Excel is a versatile part of the data analyst’s tool kit.

Like data? My book Practical SQL: A Beginner’s Guide to Storytelling with Data from No Starch Press offers a step-by-step guide to working with databases. Learn how to wrangle the everyday data you encounter to gain meaningful insights.

68 responses to “Excel: Extract text with FIND and MID”

  1. salomsalom says:

    When I have this problem I always use code to solve it. I prefer to parse every row with PHP or Python and then clean the data using the functions available on the language. I supposed everyone use the tool they feel better using it.

  2. Anthony says:

    Agreed — if you’re a coder, that’s a good way to go. I like using regular expressions, for example, or string functions in Python.

    For those people who don’t code but use Excel quite a bit, this is an approach they can take without too much pain.

  3. sbakke says:

    This was super helpful! Thank you!

  4. Michael says:

    This was great..thank you!

  5. Lyla says:

    Excellent! Now I can move on to the truly horrific part of my project.

  6. Anthony says:


    I hope it’s not that bad!

  7. cam says:

    very helpful
    thank you

  8. troy says:


    Thanks man! Perfect to what I need.


    May I ask why coders and programmers with years and decades of experience feel the need to hijack every one else’s blog posts? I am new to coding and programming. I searched for (and found) this post which does exactly what I need it to.

    If you want me to know how you do it why not write your own blog post so beginners like me have a choice of ways to accomplish this? Otherwise your comment adds nothing regardless of how much you know.


  9. Samuel says:

    Sean Burnett,RP,30,200,Dunedin, FL, 2350000.

    What if the string is as shown above. How do we extract “Dunedin” using excel text functions??

    Thanks in advance

  10. Anthony says:


    I don’t have a ready answer, but whatever the answer is it’s going to be clunky. See this discussion:


    Nevertheless, if that’s the string you’re given, then it’s simply a comma-delimited list that you can separate into columns using Excel’s text-to-columns feature. I’d go that route before trying to compete in Excel gymnastics.

  11. Michael says:

    This has been really useful, thank you!

  12. anne says:

    this is exactly what i needed! thanks! excel for the win ๐Ÿ™‚

  13. Wim Vaasen says:

    Very instructive, solved my issue.


  14. Jeff says:

    This has set me on the right path to solving my problem. I am taking specific pieces of data from a huge chunk of html pulled into the document.

    I have a unique code on each piece of data to start the search at but it appears that if I used a FIND term which is not unique, like “The” or “0” which appear in the data multiple times… it just returns #VALUE!

    However if I continue typing after “The” until it becomes a unique string it works… for that single piece of data alone. It is very frustrating.

  15. Anthony says:


    Extracting data from big pieces of HTML might be a task better served by using Regular Expressions inside some type of scripting language, such as Python.

  16. Amanda says:


    You need Python, and Beautiful Soup, and probably some love from the StackExchange community!

  17. Maria says:

    Very helpful information! Made task easy to accomplish!

    Thank you very much!

    Maria P.

  18. Mary says:

    Nicely explained. Since im just learning excel, here a little question: Could i use LEFT/RIGHT function to do this? From what i understand, these functions require one less argument (i hope this is right, i mostly have my knowledge from this beginners site: http://www.excel-aid.com/the-excel-left-and-right-functions-2.html, i hope the information given there is correct). So i could maybe use FIND with one of these functions, then return the city name plus some other data that is still left (either to the left or the right, depending on the function you use). Then i have something like “NAME: Sean Burnett POS: RP AGE: 30 WT: 200 BORN: Dunedin, FL” which i could use the RIGHT function on in cunjunction with FIND, and that would give me the city name. Of course, this is impractical, and i would have to temporarily store unnecessary data (probably in a hidden column), but i was curious if i get the concept. Sorry for my bad english btw. ๐Ÿ™‚

  19. Sujo says:

    Thank You Very Much for the detailed explanation of both the function. It really helped me.

  20. Anthony says:


    It’s possible, though I haven’t tried with this example.

  21. Purshottam says:

    Canada (CA) – Quebec – Bromont – 2 De L’Aeroport Blvd || Bromont PQ CA

    Hi Anthony,

    I have an above example where I want to extract the Country, State and City from in three seperate columns.
    In the above example the First one is Country (Canada (CA)) and Second one after first “-” (Quebec)is State and the third one after second “-” (Bromont) is City
    Can you please help me to make a formula to get the result for all three.

  22. Anthony says:


    You should be able to do it if you simply apply the techniques in the post. You’ll just have to write three separate formulas — one in each of three cells — to pull in the three values.

  23. Kapil says:

    Hi Anthony,

    How to extract numeric value from below text in excel :

    Please help me to make a formula to get the result

  24. lourdiman says:

    please help me to calculate two different times and dates which are in one cell with some other data…how can isolate or seperate them from the others data.i believe that the calculation will be bettrt and easier when the calculation data are isolated.what do you mean,do anyone knows?

  25. serajian says:

    hello. what is the program(formula) for finding the third word in a sentence?

  26. Rahul says:

    thanks Sir
    its really very useful and wonderful

  27. Alex says:

    thanks this method worked a treat for me – although i’m dealing with messier data so have some more work to do and might need to use a bit of the old manual labor!

  28. Anthony says:


    Understood. I’ve resorted to the brute-force method many a time with data!

  29. Jennifer says:

    Hi Anthony.

    With mid and find can you do an “OR” option.
    Example- say the weight was either in kgs or lbs and I
    want it to find WTKG or WTLB in the in the middle of the string.

    NAME: Sean Burnett WTKG: 200 KGBORN: Dunedin, FL
    NAME: Tyler Clippard WTLB: 200 LBS BORN: Lexington, KY
    NAME: Ross Detwiler WTKG: 174 KG BORN: St. Louis, MO NAME: NAME: Christian Garcia WTLB: 215 BORN: Miami, FL

  30. Anthony says:


    Yes, if you nest them. See if this helps:

  31. Jennifer says:


    Thank you. Very helpful.

  32. Srikanth says:

    A4 Residual Hills F1
    A5 Aluvial Plain #VALUE!
    A6 Hills Residual F1
    A7 harvest Hills F1
    A8 Plain Water #VALUE!
    A9 Structural Hills F1
    A10 Land use #VALUE!
    A11 Residual Plain #VALUE!

    Please solve my problem =IF(FIND(“Hills”,A4),”F1″,IF(FIND(“Plain”,A4),”F2″,IF(FIND(“Land”,A4),”F3″””)))
    in this wherever Hills are the that should be f1.

  33. ashish mehra says:

    If you want to know more about “Searching for text in Excel”, check this link ……..


  34. David says:

    Thanks for the help on FIND function!

    I was glad to see the Nationals make it to the playoffs this year!
    I went to the same college that Jordan Zimmerman went to, UWSP, and the small town he is from is about 20 minutes from Stevens
    Point! I actually dated a gal from Auberndale! I was very proud
    to see him get a no-hitter on the last day of the regular season!
    Made Point prouder I am sure!

  35. yash says:

    Service Request/Sales Order Handling/201295/4601491 QUART_POBLET_B__CARCER 201407018338-S1 SWAP/Ordering/Normal

    Hi I want to extract 201407018338-S1 from the above string

  36. Abid Hussain says:

    It’s really helpful…
    exactly what i want

  37. Vinod says:

    Really good information for every student.

  38. Lucas says:

    Thank you very much!!!

    Finally somebody took the time to make an example easy to understand.

  39. Zain says:


    I have data in following format

    3):XYZ #2(3):224 #3(7):MIAMI #4(3):IND #5(15):14301-8909493-3 #6(1):8

    I want extract XYZ in column 1 and 224 in Column 2 and 14301-8909493-3 in column 3 and 8 in column 4

    Please help me on this.

  40. Aarti says:

    Hey Anthony,

    You have shared really useful tips about text extract in excel. It is very very useful for me as I have just started to learn excel.


  41. Rajesh says:

    Hey Aarti,

    Its useful for every one not only for you. Thanks Mr. Anthony to share this command. Please share more useful commands about Excel which helps to all blog readers.


  42. Mary says:

    Hi, I want to extract the Town and the State from the following, I’m trying to use a “,” as a separator but it doesn’t work. Any ideas how to do it? Thanks
    355 Country Club Drive, Downingtown, PA 19335

  43. Anthony says:


    There are probably a couple of ways to do this, and this is where Excel formulas start to get complicated. But still:

    To get the town, try:

    =MID(A1, FIND(",", A1)+2, (FIND(",", A1, FIND(", ", A1)+1)-FIND(",", A1)-2))

    To get the state, use:

    =LEFT(RIGHT(A1, 8),2)

    It’s worth taking some time, especially with the first formula, to understand the logic. We’re basically looking for the first comma, and then calculating the number of characters to extract using a nested find. Crazy, I know.

  44. Elaine says:

    Thank you for taking the time to answer this! Great source!


  45. Joseph says:

    Sir – within one cell I have up to four pieces of information. Which looks like

    Address of Installed: 23.Bridgeway.Ave
    Correct Address : 24.Bridgeway.Ave

    Address of Installed: 25.Bridgeway.Ave
    Correct Address : 26.Bridgeway.Ave

    Sometimes the cell has one of these sometimes it has 5. How do I extract Installed addresses into one cell, and the correct addresses into another cell without using VBA?

    Thank you,


  46. Tasia says:

    I want to extract the check number from this string of information (This is generic info that shows you what I’m looking at):

    ORIG CO NAME:BCBS ILLINOIS ORIG ID:99999999 DESC DATE: CO ENTRY DESCR:HCCLAIMPMTSEC:CCD TRACE#:0101010101010 EED:199999 IND ID:C15272E54732230 IND NAME:CP20150929E547322300-1 TRN*1*C162547E85472617*1357954792*CP 20151104E224821515-1962644807\ PAYABLE TRN: 9574575158FB

    The information that I need is after the TRN*1* until the next *. The C162547E85472617. The check numbers are not always the same length. All of the formulas that I have tried are not giving me what I’m looking for. Any suggestions?

  47. Manny says:

    Lots of help! Thanks! ๐Ÿ™‚

  48. ron says:

    Hello, Iโ€™m trying to build a formula that will extract all of the 5 digit zip codes that appear randomly in a test string from one cell. Hereโ€™s an example of a test string: delete 96360 , 96361 x 1 , add 96361 x 2 can only have 1. The text strings are completely free text with little or no constants other than the length of the values Iโ€™m trying to extract. If the formula would just extract each of the 5 digit zips, and return each with a space in between, that would be ideal. So the formula result for the above would be: 96360 96361 or 96360 96361 96361? -Thanks, Ron M.

  49. Giorgos Soulis says:

    Thank you man! Exactly what i was looking for! Keep contributing!

  50. Terence says:

    Thank you for posting this. Exactly what I needed.

  51. Dwarika Mohanty says:

    Hi I need to extract digit(69111) after count: and before },{value here is my format .


    Dwarika Mohanty

  52. Anil Khatri says:

    I am looking this information for a long time and finally got it. Thanks a lot Mr. Anthony to post this valuable knowledge.

  53. Suresh says:

    This is what i am looking for, but any possibility in Vba macro code?

  54. Andy says:

    Hi, I need to extract the number value after “Total:” Which always appears last under 1 to 4 lines

    “WIDGET (Amount: 950.00 USD, QUANTITY: 1, PLEASE SELECT: 10 x 20 LARGE)
    EXTRA (Amount: 400.00 USD, PLEASE SELECT: 10 x 10 EXTRA, Quantity: 1)
    POWER (120 outlet Free) (Amount: 200.00 USD, Quantity: 1,
    Total: 1,550.00”

    Thanks Andy

  55. Andy says:

    How would you extract a number at the very end of a multi line text/number cell

  56. Dore Minatodani says:

    Thank you so much for this!

  57. Lets say you want to extract “or” from a string but it also contains words like for which you want to exclude, how would you do it?

  58. No I worked it out use ” or”

  59. Ruth says:

    This was perfect, thank you so much!!!

  60. kailash sharma says:



  61. Eddie says:

    Hi Anthony,

    I need some help organizing some excel data. I need to associate the multiple items to a single reference number and list them in one raw. Each item has a unique number and a few of these items can be linked to a single Reference number. Here is an example of what my spreadsheet looks like.

    Ref Number                      Category        Item Numbers
    KBMJ-58PE8930-HH080985-012   -  54               40-54-6213
    KBMJ-58PE8930-HH080985-012   -  54               40-54-6218
    KBMJ-58PE8930-HH080985-012   -  97               40-97-6211
    KBMJ-58PE8930-HH080985-012   -  36               40-36-6231A
    KBMJ-58PE8930-HH080985-012   -  97               40-97-6215
    KBMJ-58PE8930-HH080985-012   -  36               40-36-6211
    KBMJ-58PE8930-HH080985-012   -  54               40-54-6222
    KBMJ-58PE8930-HH080985-012   -  97               40-97-6233
    KBMJ-58PE8930-HH080985-006   -  41                40-41-6218A
    KBMJ-58PE8930-HH080985-006   -  67                40-67-6233A
    KBMJ-58PE8930-HH080985-006   -  41                40-41-6219
    KBMJ-58PE8930-HH080985-006   -  54                40-54-6213
    KBMJ-58PE8930-HH080985-006   -  67                40-67-6233B
    KBMJ-58PE8930-HH080985-006   -  67                40-67-6232
    KBMJ-58PE8930-HH080985-006   -  67                40-67-6243
    KBMJ-58PE8930-HH080985-006   -  54                40-54-6223

    So, I need to be able to list the Ref number only once and list in one raw all items with the same category one after the other, at the same time duplicate the Ref number for each new category. This is an excel spread sheet with 6500 records which will continue to grow exponentially. I’ve tried using the VLookup, HLookup, index and match and all I’ve manage to get to this point is a massive headache. I’ve also recreated it in Access but I have not used it for about 10 years so I can’t remember how to do it there either. Help!!!

  62. Rakesh says:

    Anthony Sir,
    Thank you very much.
    Your post was really helpful to solve my problem.
    God Bless all.

  63. Della Jacot says:

    Best post on the subject matter I’ve come upon. Many other posts tell you to make sure your text is formatted a particular way. Your information (sir) shows us how to do it with the text as is. You are the master!

    Thank you very much!

  64. Anthony says:

    Ha! Glad to help, Della.

  65. Ellen says:

    This was a huge help on a project, saved me at least 2 days.
    Thank you for posting and examples!

  66. Anthony says:


    Glad it helped!

  67. Vivian says:

    Hi Anthony:

    I try to extract the all group of words such as Grad Candidate: Spring 2017 by using if statement. If it is true then extract Grad Candidate: Spring 2017, if is false then extract Freshmen Cohort Fall 2012 from a cell content as the following.
    Adv Comm: Mathematics & Statistics, Advising Group – Senior w90, Freshmen Cohort Fall 2012, Grad Candidate: Spring 2017, Program: Undergraduate Degree.

    Thanks in advance for your advise



  68. Jeremy says:

    Just saved me hours of work. Thank you sir!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.