Extract Text Before Character in Excel (4 Quick Ways)

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.


Practice Workbook

Download the following workbook and exercise.


4 Quick Methods to Extract Text Before Character in Excel

1. Use of LEFT and FIND Functions to Extract Text Before Character

The LEFT function is one of the sub-categories of 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.

Use of LEFT and FIND Functions to Extract Before Character

STEPS:

  • Select Cell D5.
  • Type the formula:
=LEFT(C5,FIND("_",C5)-1)

Use of LEFT and FIND Functions to Extract Before Character

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.


2. Insert 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.

Insert-Excel-SUBSTITUTE-Function-Before-the-nth-Occurrence-of-a-Character

STEPS:

  • Select Cell C5.
  • Type the formula:
=LEFT(B5,FIND("^",SUBSTITUTE(B5," ","^",2))-1)

SUBSTITUTE Function Before the nth Occurrence of a Character

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

Formula:

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

SUBSTITUTE Function Before the nth Occurrence of a Character

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.


3. Apply Excel Find and Replace Tool to Extract Text Before 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 “#”.

Find and Replace Tool to Extract Text Before Character

STEPS:

  • Select cell B5:B11.
  • Press Ctrl+C to copy it and Paste it to cell C5.

Find and Replace Tool to Extract Text Before Character

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

Find and Replace Tool to Extract Text Before Character

  • 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 “#”.

Find and Replace Tool to Extract Text Before Character

  • 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.


4. Use 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 (*).

‘Text to Column’ Feature to Pull Out Text in Excel

STEPS:

  • Select cell B5:B11 and press Ctrl+C to copy it.
  • Paste it to Cell C5.

‘Text to Column’ Feature to Pull Out Text in Excel

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

‘Text to Column’ Feature to Pull Out Text in Excel

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

  • Now in 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.

‘Text to Column’ Feature to Pull Out Text in Excel

  • Here we can see all the extracted data in two portions.


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.


Related Readings

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo