There are quite a few functions in Excel to extract text before the character quickly. In this article, we are going to know how to use them.
1. Using LEFT and FIND Functions to Extract Text Before Character in Excel
The LEFT function is one of the sub-categories of the TEXT functions that can pull out the leftmost texts of a string of the dataset given. Here we are going to use the combination of the LEFT function and the FIND function. Assuming we have a worksheet containing the list of the employee names and their sales amount attached by a character “_”. We are going to extract the text before that character.
STEPS:
- Select Cell D5.
- Type the formula:
=LEFT(C5,FIND("_",C5)-1)
The FIND function returns the position of the character “_” as a number from the whole text string and the LEFT function extracts the texts.
- 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
2. Inserting Excel SUBSTITUTE Function Before the nth Occurrence of a Character
To find the nth position of a specific character and extract texts before that, we can use the SUBSTITUTE function. It’s a very popular function. Let’s say we have a dataset. We are going to extract texts before the second space of the string.
STEPS:
- Select Cell C5.
- Type 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 discovers the position of the character “^” as a number. Finally, the LEFT function pulls out the texts before that character as we discussed in the first method.
- 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
3. Applying the Excel Find and Replace Tool to Extract Text Before the Character
In Microsoft Excel, there are lots of impressive and built-in tools or features. Find and Replace is one of them. from the below dataset, we are going to extract texts before the character “#”.
STEPS:
- Select cell B5:B11.
- Press Ctrl+C to copy it and Paste it to cell C5.
- Select the pasted data.
- From the Home tab, go to Editing > Find & Select > Replace.
- A 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 “#”.
- Now keep the Replace with box blank
- Select Replace All.
- A confirmation box shows up.
- Select OK and close the previous window.
- Lastly, we can see that all the texts are extracted before the character.
Similar Readings
- 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
4. Using the ‘Text to Column’ Feature to Pull Out Text Before a Character in Excel
The Text to Column option in Excel makes the dataset dynamic. Imagine we have a dataset and we are going to extract the texts before the character called Asterisk (*).
STEPS:
- Select cell B5:B11 and press Ctrl+C to copy it.
- Paste it to Cell C5.
- Go to the Data tab by selecting all the pasted data.
- From the Data Tools drop-down, click on the Text to Columns.
- From the Wizard Step 1 window, select the option Delimited.
- Press Next.
- Now in the Wizard Step 2 window, make sure to check the Other option and write “*” in the blank box beside it. We can see the preview from the Data Preview box.
- Select Next.
- Finally, in the Wizard Step 3 window, we can select any data format we want.
- In the Destination box, select the place where we want the extracted data.
- Select Finish.
- Here we can see all the extracted data in two portions.
Download Practice Workbook
Download the following workbook and exercise.
Conclusion
These are the quickest ways to extract text before the character in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
