Ultimate Excel Text Tips: Using the MID Function to Extract Substrings

When working with text data in Excel, you may often need to extract specific parts of a text string, such as extracting a name from an email address or extracting a product code from a description. The MID function in Excel can help you achieve this by extracting a specified number of characters from a text string, starting at a specified position.

Table of Contents

Complete Excel Lessons

In this tutorial, we’ll explore how to use the MID function to extract substrings from text in Excel.

MID Function Syntax

The MID function in Excel has the following syntax:

=MID(text; start_num; num_chars)

text: The text string from which you want to extract the substring.
start_num: The position of the first character you want to extract. The first character in the text string is 1.
num_chars: The number of characters you want to extract.

=MID(A1; 8; 3)

The formula above will get the text from cell A1, start extracting from 8th character, and extract 3 characters (red).

Dynamic MID functions

Extracting a Name from an Email Address

In most cases, we do not know at which character exactly we want to extract text, especially when it comes to text of different length. In those cases, we will use combo of MID and other functions to create dynamic text extracting formula.

Suppose you have an email address in cell A1, and you want to extract the name part before the “@” symbol. You can use the following formula:

=MID(A1; 1; FIND("@"; A1) - 1)

This formula uses the FIND function to locate the position of the “@” symbol in the email address, and then extracts the characters before that position using the MID function.

Extracting a Product Code from a Description

Suppose you have a product description in cell A1, and you want to extract the product code that appears between parentheses. You can use the following formula:

=MID(A1; FIND("("; A1) + 1; FIND(")"; A1) - FIND("("; A1) - 1)

This formula uses the FIND function to locate the positions of the opening and closing parentheses in the description, and then extracts the characters between these positions using the MID function.

Extracting a Product Code from a Description

You can combine the MID function with other functions like FIND, SEARCH, and LEN to extract more complex substrings from text strings.
If you want to extract a substring from the end of a text string, you can use the LEN function to determine the length of the text string and then calculate the start position accordingly.

By using the MID function along with other Excel functions, you can efficiently extract substrings from text strings, making it easier to work with text data in your Excel worksheets.