How to Extract Text Before Character in Excel?

There are a number of functions in Excel we can use to extract text before a character quickly.

Method 1 – Using LEFT and FIND Functions

The LEFT function is a TEXT function that extracts the leftmost text from a string. We can combine the LEFT function and the FIND function to extract text from the left of a character in a string. From the List column below, let’s extract the text before the “_” character.

STEPS:

Select Cell D5.
Enter the formula:

=LEFT(C5,FIND("_",C5)-1)

The FIND function returns the position of the character “_” in the string and the LEFT function extracts the text.

Hit Enter to see the result.
Use the Fill Handle to see the rest of the results.

Method 2 – Using the SUBSTITUTE Function Before the nth Occurrence of a Character

To find the nth position of a specific character and extract text before that, we can use the SUBSTITUTE function. In the dataset below, let’s extract the text before the second space in the strings.

STEPS:

Select Cell C5.
Enter the formula:

=LEFT(B5,FIND("^",SUBSTITUTE(B5," ","^",2))-1)

➤ NOTE: Here the SUBSTITUTE function replaces the second space with the “^” character.

Formula:

=SUBSTITUTE(B5," ","^",2)

The FIND function returns the position of the character “^” as a number. Then the LEFT function extracts the text before that character.

Hit Enter.
Drag down the cursor to the other cells to see the result.

Method 3 – Using the Find and Replace Tool

From the below dataset, let’s extract texts before the character “#” using the Find and Replace tool.

STEPS:

Select cells B5:B11.
Press Ctrl+C to copy them.
Paste to cell C5.

Select the pasted data.
From the Home tab, go to Editing > Find & Select > Replace.

A Find and Replace window opens up.

In the Find what box, type “#*”.

➤NOTE: We use Asterisk (*) here as it’s a wildcard character that represents all the characters after “#”.

Keep the Replace with box blank.
Select Replace All.

A confirmation box opens.

Select OK and close the previous window.

All the text before the character is extracted.

Similar Reading

Method 4 – Using the ‘Text to Column’ Feature

The Text to Column option in Excel makes the dataset dynamic. Let’s extract the text before the Asterisk (*) in the table below using this feature.

STEPS:

Select cells B5:B11 and press Ctrl+C to copy them.
Paste to Cell C5.

Select all the pasted data.
Go to the Data tab.
From the Data Tools drop-down, click on Text to Columns.

From the Wizard Step 1 window, select the option Delimited.
Click Next.

In the Wizard Step 2 window, check the Other option and enter “*” in the blank box beside it. A preview is shown in the Data Preview box.
Click Next.