Skip to Main Content

go to Indiana University Indiana University


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.

SEARCH(find_text,within_text,[start_num])

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


3 Responses to "Use the Mid and Search Functions to Extract Characters from a String" to “Use the Mid and Search Functions to Extract Characters from a String”

  1. Jenni Says:

    Have you tried biterscripting for processing spreadsheet files ? There is a good sample script at http://www.biterscripting.com/SS_CSV.html.

    I am project mgr. I love biterscripting as it makes it very easy to transfer data between web pages and spreadsheet documents.

    Jenni

  2. kurt Says:

    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 –

    Kurt

  3. How to Create Your Own Maps « Stumbling Through the Future Says:

    […] extract the name of the LGAs into the cells in which I wanted to place it, so I had to use the ‘mid’ and ‘search’ functions. I fiddled around with the spreadsheet and produced the desired results in a reasonable […]

Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.