How to Extract the First Word from a Text String in Excel (3 Easy Ways)

Excel has some wonderful formulas that can help you slice and dice the text data.

Sometimes, when you have the text data, you may want to extract the first word from the text string in a cell.

There are multiple ways you can do this in Excel (using a combination of formulas, using Find and Replace, and using Flash Fill)

In this tutorial, I will show you some really simple ways to extract the first word from a text string in Excel.

Extract the First Word Using Text Formulas

Suppose you have the following dataset, where you want to get the first word from each cell.

Dataset to Extract the first word from text string

The below formula will do this:

=IFERROR(LEFT(A2,FIND(" ",A2)-1),A2)

Formula to find and extract only the first word

Let me explain how this formula works.

The FIND part of the formula is used to find the position of the space character in the text string. When the formula finds the position of the space character, the LEFT function is used to extract all the characters before that first space character in the text string.

While the LEFT formula alone should be enough, it will give you an error in case there is only one word in the cell and no space characters.

To handle this situation, I have wrapped the LEFT formula in the IFERROR formula, which simply returns the original cell content (as there are no space characters indicating that it’s either empty or has only one word).

One good thing about this method is that the result is dynamic. This means that in case you change the original text string in the cells in column A, the formula in column B will automatically update and give the correct result.

In case you don’t want the formula, you can convert it into values.

Extract the First Word Using Find and Replace

Another quick method to extract the first word is to use Find and Replace to remove everything except the first word.

Suppose you have the dataset as shown below:

Dataset to Extract the first word from text string

Below are the steps to use Find and Replace to only get the first word and remove everything else:

  1. Copy the text from column A to column B. This is to make sure that we have the original data as well.
  2. Select all the cells in Column B where you want to get the first wordCopy the text to Column B
  3. Click the Home tabClick the Home tab
  4. In the Editing group, click on Find and Select option and then click on Replace. This will open the Find & Replace dialog box.Click on Replace
  5. In the Find what field, enter  * (one space character followed by the asterisk sign)Enter Space character followed by asterisk in Find what field
  6. Leave the Replace with field emptyLeave the replace with empty
  7. Click on Replace All button.Click on replace all

The above steps would remove everything except the first word in the cells.

result after find and replace

You can also use the keyboard shortcut Control + H to open the Find and Replace dialog box.

How does this work?

In Find what field, we have used a space character followed by the asterisk sign. The asterisk sign (*) is a wild card character that represents any number of characters.

So when we ask Excel to find cells that contain space character followed by the asterisk sign and replace it with blank, it finds the first space character and removes everything after it – leaving us with the first word only.

And in case you a cell that has no text or only one word with no space characters, the above steps would not make any changes to it.

Extract the First Word Using Flash Fill

Another really simple and fast method to extract the first word using Flash Fill.

Flash Fill was introduced in Excel 2013 and is available in all the versions after that. It helps in text manipulation by identifying the pattern that you’re trying to achieve and fills it for the entire column.

For example, suppose you have the below dataset and you want to only extract the first word.

Dataset to Extract the first word from text string

Below are the steps to do this:

  1. In cell B2, which is the adjacent column of our data, manually enter ‘Marketing’ (which is the expected result)Enter the first word in first cell manually
  2. In cell B3, enter ‘HR’Enter manually in second cell
  3. Select the range B2:B10Select the range
  4. Click on the Home tab
  5. In the Editing group, click on the Fill drop-down
  6. Click on Flash Fill optionClick on Flash Fill

The above steps would fill all the cells with the first word from the adjacent column (column A).

Caution: In most cases, Flash Fill works fine and gives the correct result, but in some cases, it may not give you the right result. Just make sure to double-check that the results are as expected.

Note: When typing the expected result in the second cell in column B, you may see all text in all the cells in a light gray color. That is the result you will get if you hit the enter key right away. In case you don’t see the gray line, use the Flash Fill option in the ribbon.

So these are three simple methods to extract the first word from a text string in Excel.

I hope you found this tutorial useful!

Other Excel tutorials you may like:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

>
shares