Use the Mid and Search Functions to Extract Characters from a String

In Microsoft Excel, it is very simple to break data into separate columns when a delimiter, such as a comma or space, can be used. This can be done from the Ribbon by using the Text to Columns command found on the Data tab.

However, what if the data is inconsistent, and there is no comma or space delimiter that can be used? This can make separating the text in a string a little more difficult. One way to handle this is to use the Mid and Search functions.

For example, you can use the Mid and Search functions to separate some data into separate columns like this:

String Data Separated into 3 Columns

Text String    Extract Data

The MID function will extract data from the middle of a string. The syntax of the Mid and Search functions look like this:

MID(text, start_num, num_chars)

  • Text is the string containing the characters to be extracted.
  • Start_num is the position of the first character to be extracted.
  • Num_chars specifies the number of characters to be returned from the text.


  • Find_text is the text you want to find.
  • Within_text is the text to be searched.
  • Start_num (optional) is the character number at which you want to start searching. (Default is 1).

Assuming that the string data begins in cell B2, here is what the first function to extract the Section # would look like:

(Column 1 extracted) =MID(B2,SEARCH(” Sec*”,B2)+1,7)

  • We are using an asterisk wildcard to find any character after ” Sec” which would include any punctuation or spaces after “Sec”. The +1 tells Excel to begin extracting the second character in quotes, which ignores the beginning space. The 7 tells Excel to extract 7 characters beginning with the “S” in “Sec.”

(Column 2 extracted) =MID(B2,SEARCH(” T*”,B2)+1,5)

  • Here we are extracting the “T” string beginning with the “T” and extracting 5 characters.

(Column 3 extracted) =MID(B2,SEARCH(” R*”,B2)+1,4)

  • Here we are extracting 4 characters beginning with the “R” string.

Once you have the data separated into columns, you can copy and paste this text as values (by using the paste and special command). Then you can perform a Find and Replace on the individual columns where necessary to remove unnecessary commas or periods, or to place zeros before numbers for consistency.


  1. kurt

    Hi Jenni –
    thanks for posting the very informative site. there is never enough info on Excel –

    i was hoping to find instructions for extracting a string of varying length rather than a set # of characters –

    Here’s the text I need to extract:
    Location: Greater New York City Area; Industry: Marketing and Advertising … Supervisor – Senior Media Planner at Horizon Media; Media Planner at Initiative … My current role has vastly expanded my technological knowlegde of …

    And I need to grab Horizon Media; the semicolon is constant throughout the column but the only common starting point is “r at” – is this doable?

    Thanks –


Leave a Reply

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