Mastering String Manipulation: The Ultimate Technique for Excel Substrings Retrieval

Quick Links

If you want to extract a substring from the left, right, or middle of your text, you can use Microsoft Excel’s

    `LEFT`

, RIGHT, MID, LEN, and FIND functions to do that. We’ll show you how.

Which Method to Use for Substring Extraction?

What method to use to extract a substring depends on where your substring is located.

To extract a string from the left of your specified character, use the first method below. To extract everything that’s to the right of your specified character, use the second method below. To extract a string from the middle of your text, use the third method below.

Get the String To the Left of Your Text

If you’d like to get all the text that’s to the left of the specified character in your cell, use Excel’s

    `LEFT`

and FIND functions to do that.

First, open your spreadsheet and click the cell in which you want to see the result.

In your selected cell, type the following function. In this function, replace B2 with the cell where your full text is and @ with the search character. The function will retrieve the entire string to the left of this character.

Then press Enter.

=LEFT(B2,FIND(“@”,B2)-1)

Your selected cell will display the result of the function, which is the full text before your specified character in your cell.

You’re all set.

Extract the String to the Right of Your Text

To get all the text that’s to the right of the specified character in your cell, use Excel’s RIGHT , LEN , and FIND functions.

Start by launching your spreadsheet and clicking the cell in which you want to see the result.

In the selected cell, enter the following function . In this function, replace B2 with the cell where your full text is and @ with the search character. Excel will extract the entire string to the right of this character.

Then press Enter.

=RIGHT(B2,LEN(B2)-FIND(“@”,B2))

You’ll see the result of the function in your chosen cell.

Simple and intuitive video editing
🎬 Nero Video:
The powerful video editing program for your Windows PC

You’re done.

Obtain a String From the Middle of Your Text

If you’d like to extract a string containing a specific number of characters located at a certain position in your cell, use Excel’s MID function.

In your spreadsheet, select the cell where you want to display the resulting string.

In the selected cell, enter the following function. In this function, replace B2 with the cell where you have the full text, 1 with the position of the character where you want to start the string selection, and 3 with the number of characters you want to extract.

Then press Enter.

=MID(B2,1,3)