How to Extract First Word in Excel in Less Than 1 Minute

Do you have a list of full names but it’s all in one column?

And you want to do something with the first name only?

There are plenty of tasks that require the first word of a cell to be separated in a column on its own.

Mail merge, sorting and filtering, and more…

In this tutorial, you learn three different methods to get the first word from a text string.

Take a look at this conversation:

It looks like Mika needs help to get the names from this list:

Gary – apples, bananas, oranges
Tina – spaghetti
Maggie – cake, cupcakes
Elena – chicken pasta
Luke
Henry
Eric – pizza, ice cream
Tony – soft drinks
Mika
Peter – beef curry, porkchop

This might only take less than a minute since there are just 10 items on the list. But what if the list was doubled to 20? Or even 100?

Open a new Excel workbook and let’s see how we can automatically do this!

Extract the first word from text using the LEFT function

The LEFT function extracts a specified number of characters from the left side of a given text string. It has two input arguments:

• text is the source string and this is usually a cell reference
• num_chars – is the number of characters that will be extracted

Since the name is just the first word of each text string in the list, you need to count all characters before the first space character ” “.

To do this, use the FIND function.

It returns the position of a given character/string within another text string. The input arguments of the FIND function are:

• find_text is the character/string you are looking for
• within_text is where Excel will look for find_text
• start_num is the starting position of the search. This is optional and by default, the search starts from the very left side.

Let’s see these two functions in action:

1. In cell B1, type:

=FIND(” “, A1)

2. Hit Enter.

The formula returns the first space character’s position. You can then subtract 1 to get the length of the name without the space which is 4.

Now, use this in the LEFT function.

3. Change cell B1 to the below formula:

=LEFT(A1, FIND(” “, A1)-1)

4. Hit Enter.

5. Fill in the rest of the rows using the Fill Handle.

Oh no! 😱

Why did some of the cells return a #VALUE! error?

Since some items on the list only have one word, the FIND function failed to identify a space within the text string.

To fix this, the formula should include a way to catch this error and return the entire text string from Column A instead.

Workaround: Fix errors if the cell contains only one word

The IFERROR function checks if the intended formula or value returns an error. If yes, it will instead use the formula in value_if_error.

Let’s try it out.

1. In cell B1, the current formula becomes the first argument of the IFERROR function and the second argument will be the input text which is just cell A1.

=IFERROR(LEFT(A1, FIND(” “, A1)-1),A1)

3. Hit Enter then fill in the rest.

Extract the first word in Excel using Find and Replace

The LEFT function works well but you can also do the same without a formula.

1. Copy the list from Column A to Column B.

2. Press Ctrl + F to open Find and Replace.

3. On the Replace tab:

• Find what: Enter ” *” (one space followed by an asterisk).
  The asterisk “*” is a wildcard character that represents any number of characters.
• Replace with: Leave it blank.

This tells Excel to select a space character and all other characters that come after. It then replaces this selection with nothing thus removing it entirely from the text string.

3. Hit the Replace All button.

Extract the first word in Excel using Flash Fill

Finally, let’s use one of the smartest features in Excel. 💡

Flash Fill detects the most likely pattern from your data set and automatically reproduces it.

First, you need to give Excel an example of your intended result. Simple patterns such as “find first word” or “get last word” need only one or two examples.

1. In cell B1, type the first word which is “Gary”. Do this for cell B2 as well.

2. On the Excel ribbon, click the Data tab then click Flash Fill.

And watch Flash Fill do its magic!

As you have just learned in the “get first word” task, you can use a formula, Find and Replace, or Flash Fill to easily work with any text string in Excel.

Now you can use this to streamline your work and become an Excel pro! 📈

But why streamline data like this?

Because it makes your work with the important functions and features much easier.

What important functions and features?

IF, SUMIF, VLOOKUP, and pivot tables.

And you learn all of those in my free 30-minute online course.

Read all about it (and enroll) here.

Other relevant resources

You can learn more about LEFT and other text-related functions such as the MID function in this tutorial.

Also, see what else you can do using the very powerful Flash Fill.