Substring In Excel

What Is Substring In Excel?

A Substring in Excel is a string present within the given larger string. But as Excel does not have an inbuilt Substring function, Text functions, LEFT, MID, and RIGHT help extract the substring of the required length from the given text. Users can use the above functions with FIND() and SEARCH(). It helps to obtain the substring preceding or succeeding a character or between different occurrences of the given character.

For example, the below table shows a list of original texts and descriptions of the substrings we must extract from the original strings.

Suppose we need to display the extracted substrings in column C. Then, we can apply the substring in Excel formula using the Excel Text functions in the target cells and achieve the required substrings. In the below substring in Excel example, the requirement is to pull a substring from the left, right, and center in rows 2:4. So, we use the LEFT(), RIGHT(), and MID() to achieve the required substrings in the target cell range C2:C4.

Top 3 Ways To Extract Substring In Excel

We can extract a substring in Excel:

• From the left of a text using LEFT().
• From the right of a text using RIGHT().
• From the center of a text using MID().

Let us see all the methods with an example.

The below image shows three tables containing a list of original texts.

And each table shows the descriptions of what substrings to extract from the specified original texts.

Let us see how to update the above tables using each Text function.

#1 – Extract Text From The Left Of A String

The Text function, LEFT, can help us extract the required substring from the left of the original string.

The LEFT() syntax is:

where

• text: The original text containing the substring we require to extract.
• num_chars: The total characters we need to extract from the left of the text as the required substring in Excel.

While the first argument is mandatory, the second is optional. Further, please adhere to the points below to ensure we properly apply the LEFT() to search substring in Excel and return it as the output.

• The second argument, num_chars, should be equal to or more than 0.
• For num_chars exceeding the text’s length, the LEFT() return value will be the given text.
• If we omit num_chars, Excel will take its default value as 1.

Let us apply the LEFT() to search substring in Excel as per the given descriptions and update the target cell range C2:C8.

• Step 1: Select the target cell C2, enter the below formula, and press Enter.

=LEFT(A2,18)

Likewise, select each target cell from cell range C3:C8, enter the required substring in Excel formula, and press Enter to view the result. Refer to the formulas provided in cell range D2:D8, used to update cells in the range C2:C8.

While the first argument in the LEFT() is the original text, the second argument shows the total characters we must extract to create the required substring. And when counting the characters, we must also count a space as a character.

We can supply the text directly or the cell reference to the given data as the text argument.

In the case of rows 7 and 8, we must supply the date and currency values as text to display the desired substring. In this case, providing cell references to the original texts will lead to the LEFT() returning incorrect data and the first digit in the currency, 5, as the substring.

#2 – Get Text From The Right Of A String

The Text function, RIGHT, can get the required substring from the right of the original text.

The RIGHT() syntax is:

where

• text: The original text containing the substring we require to extract.
• num_chars: The total characters we need to extract from the right of the text as the required substring in Excel.

While the first argument is mandatory, the second is optional. Further, adhering to the below points will ensure that we have applied the RIGHT() properly.

• The second argument, num_chars, should be equal to or more than 0.
• For num_chars exceeding the given text’s length, the RIGHT() return value will be the given text.
• If we omit num_chars, Excel will take its default value as 1.

Let us use the RIGHT() to update the second table in the above substring in Excel example.

• Step 1: Select the target cell C11, enter the below formula, and press Enter.

=RIGHT(A11,22)

Likewise, select each target cell from cell range C12:C17, enter the required substring in Excel formula, and press Enter to view the result. Refer to the formulas provided in cells D11:D17, used to update cells in the range C11:C17.

The RIGHT() logic remains the same as the LEFT(), except the character counting (including spaces) happens from the right end of the given string.

And in row 16, the reason for supplying the date value as the text remains the same as explained in the previous section.

However, unlike the case of row 8, we can provide the cell reference to the currency value as the text argument in the RIGHT() in cell C17 to obtain the required substring. The reason is that we require the last three digits of the currency, which the RIGHT() counts correctly.

#3 – Extract Text From The Middle Of A String

If it is required to pull a substring from the text’s center, use the MID().

The MID() syntax is:

where

• text: The original text containing the substring we require to extract.
• start_num: It is the position of the first character required to extract, with the counting starting from the left of the given text.
• num_chars: The total characters we need to extract from the mid of the text, starting from the specified start_num, as the required substring in Excel.

All three arguments in the MID() are mandatory. And adhering to the below points ensures we use the MID() correctly.

• For a start_num argument value exceeding the original text’s length, the MID() returns an empty text.
• Suppose the start_num argument value is less than the original text’s length, but the total of start_num and num_chars values exceeds the given text’s length. Then, the MID() return value is the characters till the original text end.
• For a start_num value less than one or a negative num_chars, the MID() return value is the #VALUE! error.

Let us use the MID() to update the third table in the above example.

• Step 1: Select the target cell C20, enter the below formula, and press Enter.

=MID(A20,20,2)

Similarly, select each target cell from cells C21:C26, enter the formula for extracting the required substring, and press Enter to view the result. Refer to the formulas provided in cells D20:D26, used to update cells C20:C26.

The MID() accepts the original string as the text argument. The second argument value indicates the position from where we need to start counting the characters (including spaces) to form the required substring. And the third argument denotes the total characters we must count to obtain the substring as per the given description.

The reference to the cell containing the original text as the text argument will work in the MID(). But the reason for supplying the date value as text in the cell C25 MID() is the same as explained previously.

On the other hand, when counting the second and third-last digits in the amount given in cell A26, the counting starts from the number 5 (ignoring the ‘$’ symbol). And excluding the comma makes the required start_num value 3.

Alternatively, we can apply the LEFT(), RIGHT(), and MID() from the Formulas tab. And for that, we must select the target cell, go to Formulas → TEXT, and pick the required function.

And once we click on the required function, the Function Arguments window will open, where we must enter the applicable argument values and click OK to obtain the substring.

For example, let us update cell C26 with the MID() from the Formulas tab.

• Step 1: Select the target cell C26, and click Formulas → Text → MID to open the Function Arguments window.

• Step 2: Enter the MID() arguments in the respective fields in the Function Arguments window. And click OK to close the window and view the MID() output as the required substring in cell C26.

Please note that the functions LEFT, MID, and RIGHT return text, even when extracting numbers.

Examples

Check out the following illustrations to learn how to use substring in Excel.

Using the Excel Text functions with SEARCH() and FIND() excel function, we can create Excel formulas to achieve the required substrings. While the SEARCH() gives a case-insensitive formula, the FIND() helps create case-sensitive expressions.

Example #1 – Get Text Before A Specific Character

The below table contains a list of text strings.

Suppose the requirement is to display the substring occurring before the ‘_’ in each original text in column B. Then, as we need a substring from the left to the underscore, we shall apply the LEFT() with the SEARCH() in the target cells.

• Step 1: Select the target cell B2, enter the following expression for obtaining the required substring in Excel, and press Enter.

=LEFT(A2,SEARCH(“_”,A2)-1)

• Step 2: Using the fill handle, implement the above formula in cell range B3:B4.

Let us see the cell B4 expression to check how the formula works. First, the SEARCH() searches for the ‘_’ position in cell A4 text (including spaces) and returns the value of 23. Next, the num_chars expression in the LEFT() deducts one from the SEARCH() output to return the value of 22. Finally, the LEFT() returns a 22-character (including spaces) substring from the left of the original string, Kevin Reed (Team Lead).

Example #2 – Extract Text After A Certain Character

The table below contains a list of text strings.

Suppose the requirement is to obtain the substring after the ‘:’ in the text strings specified in each row and display the results in column C. It implies we require the substrings from the right of the original texts. Then, applying the RIGHT() with the LEN Excel function and SEARCH functions in the target cells will get us the required substrings from the right of the given texts till ‘:’.

• Step 1: Select the target cell C2, enter the following expression for obtaining the required substring in Excel, and press Enter.

=RIGHT(B2,LEN(B2)-SEARCH(“:”,B2))

The LEN() returns the length of the cell B2 text, 16. And the SEARCH() returns the position of ‘:’ in the cell B2 text, 6. So, the num_chars expression finds the difference between the LEN() and SEARCH(), 10. And finally, the RIGHT() returns the 10-character substring from the right of the original text, Wisconsin, with a space character in front.

• Step 2: Use the fill handle to enter the formula in cell range C3:C4.

However, the original texts contain a space after ‘:‘. And so, the resulting substrings in column C contain space before the texts.

So, we can use the REPLACE() excel function, which helps replace a substring in Excel, to acquire the required substrings without the additional space characters in the target cells.

• Step 1: Introduce a new target column D, select the target cell D2, enter the below formula, and press Enter.

=REPLACE(C2,1,10,RIGHT(C2,9))

• Step 2: Implement the above formula in cell range D3:D4.

Let us check the cell D4 expression to see how the formula works.

First, the RIGHT() returns an 11-character substring of the cell C4 text, Pilgrim Way.

Then, the REPLACE(), which helps replace a substring in Excel, accepts the cell C4 data as the old_text argument and the RIGHT() output as the new_text argument. And it takes 1 and 12 as start_num and num_chars. It implies that counting the cell C4 text characters starts from “ “ in the front till the last character, ‘y’. Finally, the REPLACE() replaces the selected characters with the new text the RIGHT() returned.

Example #3 – Pull Text Between Two Occurrences Of A Character

The below table contains two text strings.    

Suppose the requirement is to draw substrings from the original texts between the mentioned ‘;’ positions in each row and display them in column D.

As we require a substring from the mid of the given text, we can apply the MID() with the FIND and SUBSTITUTE Excel functions in the target cells to obtain the required substrings.

• Step 1: Select the target cell D2, enter the below formula to achieve the desired substring, and press Enter.

=MID(B2,FIND(CHAR(96),SUBSTITUTE(B2,”;”,CHAR(96),1))+1,FIND(CHAR(96), SUBSTITUTE(B2,”;”,CHAR(96),2))-FIND(CHAR(96),SUBSTITUTE(B2,”;”,CHAR(96),1))-1)

• Step 2: Select the target cell D3, enter the below formula to acquire the desired substring, and press Enter.

=MID(B3,FIND(CHAR(96),SUBSTITUTE(B3,”;”,CHAR(96),2))+1,FIND(CHAR(96), SUBSTITUTE(B3,”;”,CHAR(96),4))-FIND(CHAR(96),SUBSTITUTE(B3,”;”,CHAR(96),2))-1)

Let us see the cell D3 expression to understand how the formula works.

The first FIND() in the start_num argument helps find the first occurrence of the specified character to identify the starting point for counting the desired substring characters. And the second and third FIND() in the num_chars expression help determine the total characters to count till the next specified occurrence of the given character.

The CHAR() returns the character ‘`’ (Grave accent). Next, the first SUBSTITUTE() substitutes the second occurrence of ‘;’ in the cell B3 text with ‘`’. Then, the first FIND() finds the position of ‘`’ in the cell B3 text and returns the position as 17. So, the start_num argument expression adds the value 1 to the FIND() output to return the value of 18.

Similarly, the second and third FIND functions form the num_chars expression in the MID(). And while the second FIND() returns a value of 31, the third returns 17. So, the num_chars expression finds the difference between the second and third FIND function outputs and then deducts a one from the difference to return a value of 13.

Finally, the MID() counts 13 characters from the 18^th position in the given text to return the required substring between the second and fourth ‘;’ as Banana;Orange.

Example #4 – How To Find Substring In Excel (Partial Match)

We can find a substring using the ISNUMBER() Excel function with the SEARCH or FIND functions, depending on whether we want to perform a case-insensitive or sensitive search.

For example, the below table contains three text strings.

Suppose the requirement is to find the substring “12GH” in the given original texts and display the output in columns B and C. Then, here is how we can apply the ISNUMBER() with the SEARCH and FIND functions within the IF() and perform case-insensitive and sensitive searches in the target columns.

• Step 1: Select the target cell B2, enter the below formula, and press Enter.

=IF(ISNUMBER(SEARCH(“12GH”,A2)),”Present”,”Not Present”)

• Step 2: Select the target cell C2, enter the below formula, and press Enter.

=IF(ISNUMBER(FIND(“12GH”,A2)),”Present”,”Not Present”)

• Step 3: Select cells in the range B2:C2 and use the fill handle to apply the formulas in cell range B3:C4.

The SEARCH and FIND functions perform a case-insensitive and sensitive search of the specified substring in each original text. If the search result returns a number, the specified substring’s first character’s position in the original text, the ISNUMBER() returns TRUE as the output. And thus, the IF() holds in the target cell, and the output is the TRUE value, Present.

All the target cells show the substring is present in the given texts, except cell C3. The reason is that the FIND() performs a case-sensitive match. And here, the substring case in the original text does not match the case of the string mentioned in the FIND(). And thus, the FIND() does not return a number, leading to the ISNUMBER() output being FALSE. As the IF condition is FALSE, its return value is Not Present in the target cell C3.

Important Things To Note

• When using Excel Text functions, LEFT or RIGHT, the num_chars must be equal to or more than 0. And if num_chars is more than the given text’s length, the functions return the text. Also, for an omitted num_chars, Excel takes its default value of 1.
• If the MID() argument start_num exceeds the given text’s length, the function returns an empty text as the substring in Excel. But suppose the start_num is less than the given text’s length, but the sum of start_num and num_chars exceeds the text string length.
• Ensure the start_num is not below one or num_chars is not negative to avoid the MID() returning the #VALUE! error.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Substring In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Substring In Excel. Here we learn top 3 ways to extract text from different parts of string, examples & downloadable excel template. You can learn more from the following articles –

• Count Characters in Excel
• CHAR Function In Excel
• Excel Formula Not Working