How to Get (Extract) Substring in Excel

iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more

If you manipulate data in Excel, you may find yourself needing to extract substrings from strings. This is a deep dive into the methods available to manipulate strings in Excel.

Overview

An Excel substring is a portion of a text in a cell. For instance, if a cell contains the words “Excel substring”, you may want to extract “Excel”, “substring”, “Excel Sub” or even “substring”.

I will show you how to extract texts from within a cell in this guide. I will use a combination of Text (LEFT, RIGHT, and MID) SUBSTITUTEand SEARCH functions to get substrings from before, end or after a specific character of a string.

Option 1: Get a Substring at the Start, Middle, or End of a String

You can get a substring from the end, middle or beginning of a string. The following sections covers how to do this.

How to Get Excel Substring from the Start of a String

You can get substrings from the left of a string in Excel using the LEFT Function.

Below is the Syntax of the LEFT Function:

LEFT(text, [num_chars]) 

Text is a (required parameter): This is the text string you want to extract the Excel substring from.
Num_chars parameter is Optional. Characters count (subsrting) you want the LEFT function to extract from the string.

To get the first 4 characters from this string, 7OV1-BK8P-QD9Y use the following formula:

=LEFT(A6,4)

The formula above assumes that the string is in cell A6. Here is the result.

You can also specify the string directly in the formula as shown below

=LEFT("OV71-BKP8-QDY9",4)

It produces the same result as shown below:

How to Get Excel Substring from the Right of a String (RIGHT)

The RIGHT function is used to get substring from the right of a string.

Here is the Syntax:

RIGHT(text,[num_chars]) 

Text (Required parameter): The text string containing the characters you want to extract.
Num_chars (Optional parameter): Characters you want RIGHT to extract right of the string.

Continuing from the previous example, to extract the first 5 characters counting from the right of this string, 7OV1-BK8P-QD9Y use the formula:

=RIGHT(A2,5)

The result in Excel:

How to Get Excel Substring Within a String (MID)

There are circumstances you may need to extract excel substring from within a string. You will need the MID function for this.

The Syntax of the MID function is:

MID(text, start_num, num_chars) 

Text (Required): The Excel string with the characters you want to extract.
Start_num (Required): 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 (Required): Specifies the number of characters you want MID to return from text.

As an example, to extract BK8P substring from from 7OV1-BK8P-QD9Y, use this:

=MID(A2,6,4)

This tells Excel to extract 4 characters from cell A2, starting from the 6th character. See the result in Excel.

Option 2: Get Substring Before or After a Specific Character

So far, this guide have covered how to extract excel substing from the beginning, within and end of a string. In this section, I will discuss how to extract a substring after a particular character.

How to Get Excel Substring Before a Specific Character

In this example, I will show you how to get all characters before the first hyphen (counting from left) in 7OV1–BK8P-QD9Y.

There are 2 steps to complete this task:

1. Use SEARCH function to determine the position of the character, “-“.
2. Use LEFT function to extract all characters from the position determined by the SEARCH function

First, I will show how to use SEARCH to determine the position of the character.

Here is the Syntax of the SEARCH function:

SEARCH(find_text,within_text,[start_num])

find_text (Required): This is the text you want to find. In our ongoing example, this will be the hyphen, “-“.
within_text (Required): The text in which you want to search for the value of the find_text argument. This is usually the cell number containing the text. For example, the cell containing 7OV1–BK8P-QD9Y.
start_num (Optional): The character number in the within_text argument at which you want to start searching.

To get the position of the first hyphen, “-” in 7OV1-BK8P-QD9Y, use the formula:

=SEARCH("-", A2)

The result is 5 as shown in the result below:

The next step is to use the LEFT function to extract all substrings before “-“. Below is the Syntax:

LEFT(text,(SEARCH(find_text, within_text))-1)

text: is a parameter of FIND. This is the original string (usually the cell containing the text)
find_text: is a parameter of SEARCH. This specifies the character you want to extract all characters before it.
within_text: Still a parameter of SEARCH. Same as text parameter in this instance.
1: The value returned by SEARCH is usually over by 1. Subtracting 1 corrects the result.

And now the formula for our example:

=LEFT(A2,(SEARCH("-", A2))-1)

To make the seemingly complex formula easy to understand, I will break it down into the 2 parts. Then put it together again.

Part 1:

LEFT(A2, num_chars)

Here, LEFT will extract num_chars from the text in cell A2. num_chars is the number of characters from the left of the text to extract.

Part 2

(SEARCH("-", A2))-1

This tells SEARCH to find the position of “-” character from the text in cell A2.

When we put it together, we replace num_chars in LEFT function with (SEARCH(“-“, A2))-1. And here is the combined formula:

=LEFT(A2,(SEARCH("-", A2))-1)

And the result in Excel:

How to Get Excel Substring After a Specific Character

In this section, I will demonstrate how to extract Excel substring after a specific character. I want to extract all characters after the first “-” in 7OV1-BK8P-QD9Y.

The formula is similar to the example in the last section. The difference is that in this section, I will replace LEFT with RIGHT function. I will also use the within_text parameter in the SEARCH function.

Here is the general syntax for extracting Excel substring after a specific character in a text.

=RIGHT(text ,(SEARCH(find_text, text, within_text ))-1)

text: Is a parameter of the RIGHT function. This is the cell containing the string you wish to extract Excel substring from.
find_text: SEARCH parameter. Specifies the character you wish to extract characters from.
text: The second text is a parameter of SEARCH function. This is the cell containing the string you are extracting from.
within_text: This is used to tell SEARCH from what character to search for find_text. In our example within_text is “-“.

And here is the formula to extract all characters after the first “-” in 7OV1-BK8P-QD9Y

=RIGHT(A2,(SEARCH("-", A2,6))-1)

A2 is the cell with the text. “-” is the character we are extracting after and 6 is the character we are SEARCHING for “-“, from the right of the original string.

Below is the result in excel.

Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Support on Ko-Fi

Option 3: Count How Many Times a Substring Appears in Excel

In the last section of this guide, I will show how to count how many times a substring appears in a text.

The Syntax to count the number of times a specific character appears in string is:

=LEN(text)-LEN(SUBSTITUTE(text, count_text,""))

text: is the main string we want to count from
count_text: is the character we want to count.

For example, to count how many times “-” appears in 7OV1-BK8P-QD9Y, use the formula:

=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))

Breaking it down:

Let’s start with the formula:

=SUBSTITUTE(A2,"-","") 

This removes the character, “-” we want to count from the source string, 7OV1-BK8P-QD9Y. The result is 7OV1BK8PQD9Y as shown below:

Next, LEN function counts the characters left after the text we wish to count has been removed from the original text.

Here is the formula

=LEN(SUBSTITUTE(A2,"-",""))

Then, we count the number of characters in the original string, with this formula:

=LEN(A2) 

The original text has 14 characters as shown below:

Finally, subtract LEN(SUBSTITUTE(A2,”-“,””)) from LEN(A2)

=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))

And the final result is 2. See the result in Excel below.

Conclusion

I hope this guide simplified Excel substring for you!

I also hope you found the Itechguide helpful. If you did, please share your experience using Excel substring at [discourse_topic_url].

You could also ask a question or leave a comment on our forum at [discourse_topic_url]. Our community will attend to you as soon as possible.

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.