Using Excel's MID function

We've learned how to use RIGHT and LEFT to extract the rightmost and leftmost characters in a string, respectively. But what if the information we want to access lies in the middle of a string?

Fortunately, Excel contains one more extremely useful string extraction function — MID. MID allows us to pull values from the middle of a string. Let's take a look at how to use it!

The syntax of MID

MID's basic syntax is as follows:

=MID(text, start_num, num_characters)

Given an input string of text, MID will go start_num characters into that string, begin pulling a substring, and go for num_characters until stopping and returning an output.

Let's take a look at some basic examples of MID used in practice:

=MID("Boston, MA 02201", 9, 2)
Output: "MA"

The above formula outputs the string "MA", because that string begins 9 characters into the full string Boston, MA 02201 and is 2 characters long.

Here's another example:

=MID("Boston, Massachusetts, USA", 9, 13)
Output: "Massachusetts"

The above formula outputs the string "Massachusetts", because that string begins 9 characters into the full string Boston, Massachusetts, USA and is 13 characters long.

MID in practice

In practice, MID is usually combined with other text-manipulation functions like SEARCH and RIGHT to dynamically extract values from sets of strings. We usually can't use MID on its own, because start_num is often unknown. For example, consider the following sheet, in which a number of US cities are listed by zip code:

Let's say we want to create a new column of data that extracts state names from each string. We can't use MID on its own, because our start_num will be different for each line (because each city name is a different length). So, we'll combine it with SEARCH to get the data we need:

=MID(B3,SEARCH(",",B3)+2,2)
Output: "MA"

The above formula is complicated, but it pulls the results we want! Let's walk through it step by step to get a feel for how it works:

First, we call a MID function on cell B3. How do we know how far into cell B3 to start pulling text? Well, we'll SEARCH for the first occurence of a comma character (,) within Cell B3, which we know comes directly before the state name. When we find it, we add 2 to the position in question, because we know the state abbreviation begins 2 characters after the comma in each string. Then, we close our SEARCH function and ask MID to pull 2 characters from its starting point, which outputs the abbreviation of the state in question.

As you can see, combining MID with other string manipulation functions can produce some powerful results, allowing us to parse input strings in a huge variety of ways. These formulas can often become complex, so it takes a while to get your head wrapped around them — but once you do, you'll be well on the way to extracting any value you want from a given string quickly and dynamically!

Here's another example of MID used in practice to pull a state name from a larger string. This time, the length of the string we're pulling out is variable — meaning that rather than a known 2-letter state abbreviation, it can be any length. This one is even more complex. Can you tell how it works?

=MID(B3,SEARCH(",",B3)+2,SEARCH(",",MID(B3,SEARCH(",",B3)+2,999))-1)
Output: "Massachusetts"

Comments