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:
- Use SEARCH function to determine the position of the character, “-“.
- 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.
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.
