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

5 things you should know about Excel MID function

As you have just seen, there's no rocket science in using the MID function in Excel. And remembering the following simple facts will keep you safe from most common errors.

1. The MID function always returns a text string, even if the extracted substring contains only digits. This may be critical if you wish to use the result of your Mid formula within other calculations. To convert an output into a number, use MID in combination with the VALUE function as shown in this example.
2. If start_num is greater than the overall length of the original text, an Excel Mid formula returns an empty string ("").

1. If start_num is less than 1, a Mid formula returns the #VALUE! error.
2. If num_chars is less than 0 (negative number), a Mid formula returns the #VALUE! error. If num_chars is equal to 0, it outputs an empty string (blank cell).
3. If the sum of start_num and num_chars exceeds the total length of the original string, the Excel MID function returns a substring starting from start_num and up to the last character.

How to extract first and last name

If you've had a chance to read our recent tutorials, you already know how to pull the first name using the LEFT function and get the last name with the RIGHT function. But as is often the case in Excel, the same thing can be done in a variety of ways.

How to get substring between 2 delimiters

Taking the previous example further, if besides first and last names cell A2 also contains a middle name, how do you extract it?

Technically, the task boils down to working out the positions of two spaces in the original string, and you can have it done in this way:

• Like in the previous example, use the SEARCH function to determine the position of the first space (" "), to which you add 1 because you want to start with the character that follows the space. Thus, you get the start_num argument of your Mid formula: SEARCH(" ",A2)+1
• Next, get the position of the 2^nd space character by using nested Search functions that instruct Excel to start searching from the 2^nd occurrence of the space character: SEARCH(" ",A2,SEARCH(" ",A2)+1)

  To find out the number of characters to return, subtract the position of the 1^st space from the position of the 2^nd space, and then subtract 1 from the result since you don't want any extra spaces in the resulting substring. Thus, you have the num_chars argument: SEARCH (" ", A2, SEARCH (" ",A2)+1) - SEARCH (" ",A2)

With all the arguments put together, here comes the Excel Mid formula to extract a substring between 2 space characters:

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

The following screenshot shows the result:

In a similar manner, you can extract a substring between any other delimiters:

For example, to pull a substring that is separated by a comma and a space, use this formula:

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

In the following screenshot, this formula is used to extract the state, and it does the job perfectly:

How to extract Nth word from a text string

This example demonstrates an inventive use of a complex Mid formula in Excel, which includes 5 different functions:

• LEN - to get the total string length.
• REPT - repeat a specific character a given number of times.
• SUBSTITUTE - replace one character with another.
• MID - extract a substring.
• TRIM - remove extra spaces.

The generic formula is as follows:

Where:

• String is the original text string from which you want to extract the desired word.
• N is the number of word to be extracted.

For instance, to pull the 2^nd word from the string in A2, use this formula:

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))

Or, you can input the number of the word to extract (N) in some cell and reference that cell in your formula, like shown in the screenshot below:

How to extract a word containing a specific character(s)

This example shows another non-trivial Excel Mid formula that pulls a word containing a specific character(s) from anywhere in the original text string:

Assuming the original text is in cell A2, and you are looking to get a substring containing the "$" character (the price), the formula takes the following shape:

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("$",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99))

In a similar fashion, you can extract email addresses (based on the "@" char), web-site names (based on "www"), and so on.

How to force an Excel Mid formula to return a number

Like other Text functions, Excel MID always returns a text string, even if it contains only digits and looks much like a number. To turn the output into a number, simply "warp" your Mid formula into the VALUE function that converts a text value representing a number to a number.

For example, to extract a 3-char substring beginning with the 7^th character and convert it to a number, use this formula:

=VALUE(MID(A2,7,3))

The screenshot below demonstrates the result. Please notice the right-aligned numbers pulled into column B, as opposed to the original left-aligning text strings in column A:

The same approach works for more complex formulas as well. In the above example, assuming the error codes are of a variable length, you can extract them using the Mid formula that gets a substring between 2 delimiters, nested within the VALUE function:

=VALUE(MID(A2,SEARCH(":",A2)+1,SEARCH(":",A2,SEARCH(":",A2)+1)-SEARCH(":",A2)-1))