Extract Part of a Text String with Excel’s MID Function

The MID function in Excel allows you to extract a specified number of characters from a text string starting at a specified position. This can be incredibly useful for isolating parts of text strings for further analysis or manipulation. In this article we’ll explore the syntax and uses of the MID function in depth.

How the MID Function Works

The MID function takes three arguments

• The text string you want to extract from
• The starting position of the characters you want to extract
• The number of characters to extract

Here is the basic structure:

=MID(text, start_num, num_chars)

Where:

• text is the text string containing the characters you want to extract.
• start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
• num_chars specifies the number of characters you want MID to return from text.

So MID allows you to specify a starting point and total number of characters to return a substring from a larger string.

Simple Examples of Using MID

Let’s look at a few simple examples to understand how to use MID in Excel:

• =MID("Alphabet",3,4) would return “phab”. It starts at the 3rd character and extracts the next 4 characters.
• =MID("Apple",1,5) would return “Apple”. It starts at the 1st character (the default) and extracts the next 5 characters.
• =MID("Hello World",7,5) would return “World”. It starts at the 7th character and extracts the next 5 characters.

So MID allows you to isolate parts of strings easily in Excel.

Extracting Parts of Cell Values with MID

You can also use MID to extract partial text strings from cell values:

• If A1 contains “Exploration”, =MID(A1,4,4) would return “lora”
• If B2 contains “Statistics”, =MID(B2,1,4) would return “Stat”
• If C3 contains “Association”, =MID(C3,4,3) would return “soc”

This allows you to extract portions of text strings based on cell references rather than directly typed text.

Using MID with Other Functions

The MID function becomes even more powerful when combined with other text and lookup functions in Excel:

• Use FIND to locate a character position, and feed that into MID to return characters after it.
• Use LEFT or RIGHT on a MID output to further extract substrings.
• Look up partial strings with MID and VLOOKUP or MATCH.

For example:

=MID(A2,FIND(" ",A2)+1,99)

This would find the space character in A2, return all characters after it.

Handling Errors and Empty Cells

• If the start_num argument is greater than the length of text, MID will return an empty string.
• If num_chars is omitted, MID will return all characters to the end of text.
• If text is an empty cell, MID will also return an empty string.

This makes MID a bit more forgiving than alternatives like LEFT and RIGHT when handling errors.

MID Formula Examples

Here are some practical examples of using MID in Excel for common use cases:

• Extract the month from a full date:

=MID(A2,4,2)  

• Extract the middle initial from a full name:

=MID(B2,FIND(" ",B2,1)+1,1)

• Extract the domain from an email address:

=MID(C2,FIND("@",C2)+1,99)

• Extract the last 4 digits of a credit card:

=MID(D2,LEN(D2)-3,4)

As you can see, MID allows you to isolate and extract really specific parts of text strings in your Excel formulas.

Using MIDB for Double Byte Character Sets

Excel also has the MIDB function, which is designed for languages that use Double Byte Character Sets (DBCS). It takes the same arguments as MID, but calculates the character length and start position differently for DBCS languages.

MID vs. LEFT, RIGHT and SUBSTITUTE

Depending on your specific needs, Excel has a few alternatives that can extract substrings in certain cases:

• LEFT extracts from the start of the string.
• RIGHT extracts from the end of the string.
• SUBSTITUTE can replace parts of strings.

However, MID is the most flexible substring extractor that lets you specify any starting position and number of characters to return.

Limitations of MID

While MID is very versatile, it does have some limitations:

• It can be slow on large datasets with many formulas.
• The character positions start at 1, which can confuse some users.
• It only understands single byte character sets by default.
• The arguments require some calculation to find start position and length.

So while MID is not perfect, it provides functionality you simply can’t get natively in Excel with other functions.

Summary

The MID function is a very handy tool for extracting a specified number of characters from within text strings in Excel. Key takeaways:

• MID allows you to specify a start position and total number of characters to extract.
• It can extract parts of cell values based on references.
• MID becomes even more powerful combined with other text functions.
• It gracefully handles errors and empty cells.
• MIDB handles Double Byte Character Sets.
• While not perfect, MID enables substring extraction not possible otherwise.

Learning to use MID will allow you to isolate important parts of strings in your Excel formulas for further analysis and manipulation. It’s a simple but versatile function worth learning.