Excel Substring Functions: Learn How to Use Them

A substring refers to a smaller string located within a larger string. Specifically, it is a string of characters at the left, right, or middle of the data. To extract an Excel substring, we can use some text-based standard formulas with fixed and variable length. We can also use some non-formula-based techniques. 

But let’s go back to the basics.

We store different types of data in cells in Excel. This data can be a number, text, date, or something else like a URL. The data itself is called string. A substring is a part of the overall data string. 

Excel Substring Functions

If you’re ever asked about an Excel substring function during an Excel interview, it’s a trick question; there isn’t one. But though there are no specific substring functions in Excel, there are still ways to extract a substring from a larger string of data. 

If we have to extract a left, right, or middle substring from a longer string of data, especially where there are thousands of records, we can use LEFT, RIGHT, and MID functions of Excel. But this is only the beginning. Combining these functions with LEN and FIND functions, we can apply almost any kind of intelligence and extract any data that we need. We can also use tools such as Flash Fill, Find and Replace, and Text to Columns.

Let’s start with the most basic Excel substring functions.

LEFT

If you need to extract a specific number of characters from the left side of a string, this is the function for you. 

All you need to input in this formula is the cell name that contains the string and the number of characters that you want to extract. 

= LEFT(REFERENCE, CHARACTERS)

RIGHT

If you need to extract a specific number of characters from the right of a string, the function you want is RIGHT. Again, all you need to input in this formula is the cell name that contains the string and the number of characters that you want to extract. 

= Right(REFERENCE, CHARACTERS)

MID

If you need to extract a substring from the middle, use the MID function in Excel. It requires you to input the cell name that contains the string, the character number from which you need the data, and the number of characters that you need to extract.

= Mid(REFERENCE, CHARACTERS)

Did you notice that in each of the above formulas, we specified a specific number of digits that we need to extract? This is called hardcoding a number. Hardcoding can only bring the right answer if the string has the same length in each row, and so, we only need to extract the same number of characters in each row.

But that is not always the case, and we will usually need to extract dynamic characters from the string.  

Using the LEFT Function With a Dynamic Character Count

The LEFT function requires you to enter the number of characters from the left that you want to extract. However, often we need to extract data before a specific character like a space, period, comma, or @ sign. 

To extract a variable length Excel substring before a specific character, we can combine FIND function with LEFT function.

What is the FIND function? This function finds a specific character in the string and returns the number at which this character is located. 

So, to extract the substring of dynamic length from the left of the string, we need to use the LEFT function, and in the place of number of characters, we need to use FIND function.

= LEFT(Cell, FIND(“Character”, Cell)-1)

We have to do -1 as we do not want to bring in that special character. 

Using the RIGHT Function With a Dynamic Character Count

To calculate the dynamic length from the right, we first need to calculate the total length using the LEN function, and then deduct the length of text before the specific character (that we calculate with the FIND function). This leaves us with a number of characters in the substring, which is on the right of the special character.

• LEN. Returns the total length of the string – that is, the number of characters in the complete string.
• FIND. Returns the number of characters in the substring up to the specific character.
• LEN – FIND. Returns the number of characters in the substring after the specific character.

So, to extract a substring using the RIGHT function, we need to use the RIGHT function and then, in the place of the number of characters, we need to use (LEN-FIND).

Finding MID Between Special Characters

MID functions allow us to enter the character number from which our new substring should start and at which it should end. To pick the data between two special characters, we need to calculate the character number of the special characters. We can do this with the FIND function, though that is a little circuitous.

To count the characters up to the first special character, simply use the FIND function to calculate the character number. Assuming the special character is left parenthesis, or “(“, it would look like this:

=FIND(“(“, cell)+1

+1 here makes sure we do not bring back the ( sign.

To count the characters up to the second special character, use the FIND function to count the character number of the second character. Here, we need to deduct the count of the first special character so that only middle characters are returned.

Assuming the second special character is “)”, and the first special character “(“, it would look like the following:

= ((FIND(“)”, cell) – 1 ) – FIND(“(“, cell))

So you can see that we can almost extract any kind of substring using the LEFT, RIGHT, and MID functions. But that’s not all! Here are some more workarounds that let you extract substrings; they are a bit lengthy to use, but these are formula free and do the job. They are “flash fill” and “text to columns.”

Extracting Excel Substring With Text to Columns

If we have to extract a substring before a special character, using the ‘Text to Columns’ option in Excel in a very easy and no-formula way. Let’s discuss this with the help of an example.

Let’s say you have to extract names from the email address. We can say that anything before the @ sign is the name. So, to extract the names from the email addresses, just select the column that contains the email addresses and then navigate to Formula Bar > Data tab > Text to Columns.

Once you click there, the following screen will appear. Select Delimited and click on Next.

If your special character is a comma, a period, or a space, you can simply choose that by clicking the checkbox. For any other special character, you can click on ‘other’ and then insert the special character in the box.

Now click Next, and then click Next again. This will split your data into two columns, the first column containing names and the second containing the rest of the data.

Sometimes all the data will be imported into a single column even though it’s actually meant for separate columns. This usually happens when you extract data from a website or desktop software. 

In the below screenshot, the data that should be split over four columns has instead been imported into one column.

One possible option to extract substrings from this data is to use the “Text to Column” and “Fixed Width” functions. With this option, we can manually insert lines between the characters. 

Go to Formula Bar > Data > Text to Columns.

Choose the ‘Fixed Width’ option in the first step and click Next.

Now, click between the text to insert the lines. Each click will insert a new line, and this will split data into separate columns. To remove a line, just double-click on the line.

Now click Next and the Finish. It will take you back to your Excel workbook with your data now split into different columns.

Of course, this is only possible where the length of the substring is the same in each row. However, as software databases are designed in a very systematic manner, this option will be useful in at least some of the cases.

Extract a Substring With Flash Fill

If you manually type a substring in the cell next to the string, Excel can observe the pattern and replicate the same for you in all the cells below. This feature is called ‘Flash Fill’.

Let’s continue with the same data we were using in the last example. If we need to extract substring starting with SO with three additional characters, we just need to type these characters in the next cell.

Now click the cell B1 and go to: Formula Bar > Data > Flash Fill.

Click the Flash Fill, and Excel will fill all the cells below using the same criteria: character 6 to 10.

Let us give it one more try. Let’s write the product type appearing towards the end of the string.

Now click the Flash Fill, and the magic happens!

Extract a Substring With Find and Replace

Another quick, easy, and no-formula approach to extract the substring is to use the “Find and Replace” function to replace the unwanted string with nothing at all. 

Let’s say you have some unwanted characters in the string and you want to remove them. Select the column and press Ctrl + H on your keyboard. 

Alternatively, select the column and go to Formula Bar > Home > Find and Select > Replace.

The following pop-up should open.

Now if we have to remove SO from the Order Number, we will just fill in ‘SO’ in the ‘Find what’ box and leave the ‘Replace with’ box empty. This will replace SO with nothing – that is, this will simply remove SO in each string in the entire column.

Using Excel Substring Functions

Extracting substrings from data in Excel is quite easy, both with formulas and without formulas. All you need to do is to practice LEFT, RIGHT, MID, LEN, and FIND functions. And if you are not formula-savvy, you can use Text to Columns, Flash Fill, and Find and Replace tools to extract a substring from a data string. 

Now that you know the substring extraction in detail, the next step should be to learn to clean the data. That begins with functions such as Excel Trim.

If you wish to learn Microsoft Excel from Beginner to Advanced level with all these techniques covering importing data into Excel, cleaning data, organizing data as per best practices, analyzing data and visualizing data, and building meaningful dashboards, consider a beginner Excel course or any other course on Microsoft Excel.