How to Extract an Excel Substring? – 6 Best Methods

Note: This guide on how to extract a substring in Excel is suitable for all Excel versions including Office 365.  

In this guide, I am going to show you how to extract any kind of substring in Excel with ease. 

I’ll cover: 

1. What is a Substring?
2. Excel Substring Extraction – An Overview. 
3. How to Extract an Excel Substring of a Fixed-Length?
   • Extract Substring from the Left Side (LEFT)
   • Extract Substring from the Middle (MID)
   • Extract Substring from the End (RIGHT)
4. How to Extract Excel Substring of a Variable Length?
   • How to Extract Substring Preceding a Delimiter?
   • How to Extract Substring Succeeding a Delimiter?
   • How to Extract Substring Between Two Delimiters?
5. How to Check for a Substring in Excel?
6. How to Extract Excel Substring Having a Specific Text or Character?
7. Extract Excel Substring Using the Text to Columns Feature
8. Extract Excel Substring Using Flash Fill

Related: 

How To Find Duplicates In Excel? The Best Guide

Excel Goal Seek—the Easiest Guide (3 Examples)

Create A Pivot Table In Excel—the Easiest Guide

What is a Substring?

Have you ever wondered how to extract part of a text string in Excel? 

Before I tell you how to do it, let’s see what exactly a substring is. 

A substring is nothing but a part of any string. This string can be a pure text string or even be an alphanumeric string.

Let’s say, for example, I have a set of alphanumeric product codes in the format “xxxx-yyyy-zzzz”. The leftmost set of characters indicate the product category, the middle set of characters indicate the regional code and the last set of characters indicate the manufacturer’s code. 

Here, each set of four characters is a substring of the entire product code. For example, “xxxx” is a substring of “xxxx-yyyy-zzzz”. 

Now, you will come across situations where you need only part of a code or text. For example, in my case, I need to find the region codes of products from the product codes. To do this, first I need to extract the region substring from the product code. 

Excel Substring Extraction – An Overview.

The most efficient way to extract an Excel substring is to use the Text functions, as there are no dedicated Excel substring functions. 

I’ll cover all possible scenarios of substring extractions and explain each one of them with examples. 

How to Extract an Excel Substring of a Fixed-Length?

Excel has three text functions for extracting text of a certain length from the left, middle and right of a string. They are: 

1. LEFT 
2. MID
3. RIGHT

Extract Substring from the Left Side (LEFT)

To extract a substring of a certain length from the left side of a text, use the LEFT function. 

Here in this example, I enter =LEFT(A2,4) to extract the first four characters of the product code from its left-hand side. 

Extract Substring from the Middle (MID)

To extract a substring of a certain length from anywhere in the middle of a string, use the MID function. 

Here in this example, I enter =MID(A2,6,4) to extract four characters starting from the 6th character of the product code. 

Extract Substring from the End (RIGHT)

To extract a substring of a certain length from the end of a string, use the RIGHT function. 

For example, I enter =RIGHT(A2,4) to extract the last four characters of the product code. 

Also Read: 

How To Use Excel Countifs: The Best Guide

Excel Conditional Formatting -the Best Guide (Bonus Video)

The Best Excel Project Management Template In 2021

How to Extract Excel Substring of a Variable Length?

In some cases, things may not be as simple as this. The substring may not have a fixed length.

We cannot use LEFT, RIGHT and MID functions directly in such cases, as these functions need a fixed substring length. 

But, we can use a combination of these along with SEARCH or FIND functions, to extract the substring. Also, it helps a lot if the original text string has some kind of delimiter in it which separates the substrings. For example, a space or a hyphen will do the trick. 

Please note that SEARCH is not case-sensitive whereas FIND is case-sensitive. 

How to Extract Substring Preceding a Delimiter?

To extract a substring that precedes a delimiter, use the formula 

= LEFT(cell_reference, SEARCH(“Delimiter”, cell_reference)-1) inside an adjacent cell and drag it to the entire range of cells. 

Here, the “Delimiter” can be any character in the string you choose and “cell_reference” is the cell reference of the original text string. 

In this formula, the SEARCH function searches for the delimiter and returns its position in the string. Then the LEFT function extracts the substring up to the delimiter’s position. 

Note: Here, the SEARCH function returns the position of the first instance of the delimiter. 

How to Extract Substring Succeeding a Delimiter?

To extract a substring that succeeds a delimiter, use the formula 

RIGHT(cell_reference,LEN(cell_reference)-SEARCH(“Delimiter”, cell_reference)) inside an adjacent cell and drag it to the entire range of cells. 

In this formula, the SEARCH function searches for the delimiter and returns its position in the string. Then, the RIGHT and LEN functions extract the substring after the delimiter’s position. 

Note: Here, the SEARCH function returns the position of the first instance of the delimiter. If you have more than one delimiter and want to extract the substring after the last delimiter, using the text to columns feature is an easier option. 

How to Extract Text Between Two Delimiters?

The easiest way to extract a substring between two delimiters is to use the text to column feature in Excel, especially if you have multiple delimiters. 

But, if you still want to use a formula to do this, use this: 

=MID(cell_reference, SEARCH(“Delimiter”, cell_reference)+1, SEARCH (“Delimiter”, cell_reference, SEARCH (“Delimiter”, cell_reference)+1) – SEARCH (“Delimiter”, cell_reference)-1)

=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1) 

Here, the MID function has three arguments: cell_reference, starting position and string length. 

In our modified MID formula, in the first two arguments, we give the cell reference and the starting position of the substring after the delimiter. 

For the last argument, we use a nested SEARCH function to find the position of the last delimiter and subtract the position of the first delimiter from it. This yields the length of the substring between the two delimiters. 

There is an alternative formula to do this, especially if you have multiple instances of the same delimiter in the string. 

Use this formula: 

=MID(cell_reference,FIND(CHAR(1),SUBSTITUTE(cell_reference,”Delimiter”,CHAR(1),N))+1,FIND(CHAR(1),SUBSTITUTE(cell_reference,”Delimiter”,CHAR(1),N+1)) – FIND(CHAR(1),SUBSTITUTE(cell_reference,”Delimiter”,CHAR(1),N))-1)

Where N and N+1 are the nth and n+1th number of occurrences of the delimiter in the original string, between which lies the substring of interest. 

For example, if you want to extract the substring between the 3rd & 4th occurrences of a delimiter, replace N and N+1 with 3 and 4 respectively. 

How to Check for a Substring in Excel?

In certain scenarios, you may need to only check whether a particular substring is present in the text or not. Do this easily with an ISNUMBER and SEARCH combo. Replace SEARCH with FIND if you want case sensitive results. 

Type in the formula =ISNUMBER(SEARCH(“substring”, cell_reference)) in an adjacent column cell and drag it to the entire data range. 

Here, the SEARCH function looks for the substring in the text and returns a numerical value denoting the position of the substring only if it is present. It returns an error if the substring is not present in the text. So, the ISNUMBER function returns TRUE or FALSE results based on the SEARCH function’s inputs. 

How to Extract Excel Substring Having a Specific Text or Character?

In situations where you are looking to extract a substring which contains a specific text or character, type this formula in the adjacent column:

=TRIM(MID(SUBSTITUE(cell_reference,” ”,REPT(“ “,100)),MAX(1,FIND(“Character”, SUBSTITUE(cell_reference,” ”,REPT(“ “,100)))-50),100)) 

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),MAX(1,FIND(".", SUBSTITUTE(A2," ",REPT(" ",100)))-50),100))

Here, “Character” is the text or character of interest you are looking for. The numbers 100 AND 50 are arbitrary. Use any large number you want, as long as you replace “50” with half of the number of your choice. 

Let me break down this formula into three simple steps. 

Step 1: SUBSTITUTE and REPT combo replace each space with 100s of spaces. 

Step 2: MID and FIND combo looks for the specific and character you are looking for and

extracts the substring which contains it. The MAX function makes sure that the MID

 function doesn’t start in a negative position for cases where the character is present in 

the first word of the text. 

Step 3: The TRIM function removes the extra spaces in the extracted substring. 

Note: This formula works only when you have a space as the delimiter in the text string. If you have any other delimiter, break it into two steps. First, use the same formula as above up to the MID function. Then, replace TRIM with another SUBSTITUTE and choose “” as the replacement. 

Extract Excel Substring Using the Text to Columns Feature

This is another easy way to extract a substring in Excel. Just select the relevant data range and click on the Text to Columns button under the Data tab. Select the relevant delimiter and click OK in the Text to Columns wizard.

There are two major disadvantages to this method. It extracts all substrings present in the string and it is not dynamic. 

Extract Excel Substring Using Flash Fill

This is arguably the easiest method to extract a substring in Excel. Just manually type in the substring for the first few instances and drag it to the rest of the data range. Let Excel Flash Fill show its magic and fill all the substrings for you. 

Note: This method is not dynamic and may not work in some cases. Also, Flash Fill is available only in Excel versions 2013 and later. 

Suggested Reads: 

Create An Excel Dashboard In 5 Minutes – The Best Guide

Dynamic Dropdown Lists In Excel – Top Data Validation Guide

Predict Future Values Using Excel Forecast Sheet – The Best Guide

Let’s Wrap Up

These are all the different Excel substring methods. If you have any questions about this or any other Excel feature, let us know in the comments. 

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.