How to Extract Substring in Excel

A substring is simply a part of a text string (or a text entry) in a cell. For example, ‘the Fury’ is a substring of ‘Ours is the Fury’.

Sometimes you may want to extract a substring from a text string in excel data. For instance, you like to extract the city name (Los Angeles) from the whole address (6854 Santee St, Los Angeles, CA 90015, United States). Sadly, there is no specific substring function in Excel that you can use to extract a substring. However, this could still be done by using a few of the in-built functions that are categorized under the TEXT function.

In this article, we’ll explain how to extract a substring from a cell using the text functions in Excel.

Excel TEXT Functions to Extract Substrings

There are three primary functions that are used to extract a substring of text from a cell.

LEFT function: It extracts a substring from the left of the text string.
RIGHT function: It extracts a substring from the right of the text string.
MID function: It extracts a substring from the middle of a string, beginning at the specified position in the text.

There are other functions also, like FIND, SEARCH, LEN which are used to find the position or length of the specified text.

Excel LEFT Function

The LEFT function is used for extracting a certain number of characters from the left of the string, beginning with the first character of the string.

The syntax for the LEFT function:

=LEFT(text,[num_chars])

Where:

text: Specifies the text string or reference to a cell that contains the characters you want to extract.
num_chars: Specifies the no. of characters you wish to extract (this includes space between the characters).

Example for LEFT Function:

Let’s assume we have an address in cell A1 of our spreadsheet. And we want only the door number and street name from the address, so we would use the following LEFT function:

=LEFT(A1,14)

The function in the above screenshot tells Excel to look at the text value in cell ‘A1′ and then extract the leftmost ’14’ characters from that text string.

How to Extract a Substring Before Specific Character(s)

If your string has a pattern, then you can use Excel LEN and FIND functions to return the specified number of characters without the need for counting the number of characters to input the second argument.

Enter the following formula to extract a substring before a specific character:

=LEFT(text,FIND("char",text)-1)

text: A string from which we wish to extract a substring, this could be a text string or a reference to a cell that has a string.
char: A specific character for which we want to determine the position.

FIND function finds the position of the specified character and subtracts 1 (character itself) from the result, and then it pulls all the characters left of it using the LEFT function.

For example, we have a list of email addresses. We want to extract only the username from the email address as substring before the ‘at sign’ (@) from cell A2, for that we’re using this formula:

=LEFT(A2,FIND("@",A2)-1)

This formula uses the FIND function to return the position of the character ‘@’, which is 9, and 1 position is subtracted to extract 8 characters from the left of the string with the help of the LEFT function.

We’ll try another example, but this time with a slightly different approach.

To get the text before a specific character or characters, this time we use SEARCH or FIND, and LEN functions. Find the position of the character with the SEARCH function, subtract that position from the total length of the string found by the LEN function, and finally extract subtracted number of characters from the left of the string with the LEFT function. You can use either SEARCH or FIND to return the position of the character. The syntax:

=LEFT(text,LEN(text)-SEARCH("char",text))

We will use the below formula to extract a substring before the word ‘The’ in our example:

=LEFT(A2,LEN(A2)-SEARCH(" The",A2))

The above formula returns the position of the characters ‘ The’ (19) with the SEARCH function, subtracts that number from the total length of the string found by the LEN function (37), and finally extracts the resulting number of characters (18) left of the string with the LEFT function.

Note: We supplied space and the word “The”) as the argument for the SEARCH function in the above formula.

If we use multiple characters in the first argument of the SEARCH or FIND, it will only return the position of the first character.

Excel RIGHT Function

To extract a substring from the right side or the end of a text string, use the RIGHT function:

=RIGHT(text,[num_chars])

Example for RIGHT Funtion:

In our example name list, we wish to extract Last Name as Substring from the right side of the Full Name using the following formula:

=RIGHT(A2,7)

The function in the above tells Excel to look at the text value in cell ‘A2’ and then extract the right-most ‘7’ characters from that text string.

How to Extract a Substring After a Specific Character(s)

If you want to get a substring after a specific character, you can use LEN and SEARCH/FIND functions together in the num_char argument of the RIGHT function to find the certain number of characters.

The syntax:

=RIGHT(text,LEN(text)-SEARCH("char",text))

Suppose we have an email list, we wish to extract Domain Name as substring from the right side of the email address using the following formula:

=RIGHT(A2,LEN(A2)-FIND("@",A2))

The example formula uses the FIND function to return the position of the character ‘@’, which is 9, and subtracts that position number from the total string length which is found by the LEN function to extract the resulting no. characters right of the string by the RIGHT function.

Excel MID Function

To extract a substring, from the middle of the text string, starting at the specific position, then try the MID function in Excel.

=MID(text,start_num,num_chars)

Compared to the other two Text functions, the MID function has a slightly different set of arguments. Instead of just specifying the original text string (text) and the number of characters to extract (num_chars), you also need to indicate starting position (start_num) in the original text string.

Example for MID Function:

In our example, we want to extract only the middle names from a list of full names, so we would use the below formula:

=MID(A2,9,4)

The above function counts the characters in cell A1 from left until it gets to the 9th character and then it returns the next 4 characters (including the 9th character).

How to Extract a Substring Between Two Characters

To extract a substring between two characters, use the following syntax:

=MID(text,FIND("char",text)+1,FIND("char",text)-FIND("char",text)-1)

text: A string from which we wish to extract a substring, this could be a text string or a reference to a cell that has a string.
char: A specific character for which we want to determine the position.

For example, let’s say we have an email address, and we want to extract the ‘domain name’ without username and .com part, so we would use the below formula:

=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)

It’s a bit confusing, so we’ll give you some details about the formula:

The text string – Eveline@firstexpress.com

A2 Specifies the cell that contains the text string from which we wish to extract the substring.
FIND(“@”,A2)+1 part of the formula finds the starting position of the substring (9), which is right after the ‘@’. So we supply ‘@’ as its character.
FIND(“.”,A2)-FIND(“@”,A2)-1 determines the length of the substring between the ‘@‘ and the ‘.‘ by subtracting the position of ‘.’ (21) from the position of ‘@’ (8) and subtracting 1 from the result.

First, FIND(“@”,A2)+1 returns the starting position of the substring (9), FIND(“.”,A2)-FIND(“@”,A2)-1 finds the number of characters (12) between the ‘@‘ and the ‘.‘, and then the MID function extracts that number of characters (substring) from the text.

Here’s a formula for another example,

=MID(A2,FIND(" ",A2)+1,FIND(" The",A2)-FIND(" ",A2)-1)

FIND(" ",A2)+1 – specifies a space character “ ” in the first argument.

FIND(" The",A2)-FIND(" ",A2)-1 – a space character and the word ‘The’ are used together as the first argument in the first FIND function; and space is used as the first argument in the second FIND function.