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.
Read More: How to Extract Text after a Specific Text in Excel
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.
Read More: How to Extract Text After First Space in Excel
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
- How to Extract Text after Second Space in Excel
- How to Extract Text After Last Space in Excel
- How to Extract Text Between Two Commas in Excel
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.
- In the Wizard Step 3 window, select any data format.
- In the Destination box, select where to place the extracted data.
- Click Finish.
All the extracted data is split across two cells.
Download Practice Workbook
Related Reading
- How to Extract Text after Second Comma in Excel
- How to Extract Text between Two Spaces in Excel
- How to Extract Text Between Two Characters in Excel
- How to Extract Certain Text from a Cell in Excel VBA
- How to Extract Text After a Character in Excel
<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
