Get first word from some text

How to extract the first word from some text? We will show you various solutions using Excel’s FIND, LEFT, and ExtractWord functions.

Today’s tutorial is a part of our definitive guide on Excel Formulas.

Generic formula to get the first word

=LEFT(text,FIND(" ",text)-1)

Explanation:

First, place the initial data set. The main point of the formula is the following:

The FIND function locates and gets the position of the first occurrence of a space (“ “) character in the given text string. The inner part of the formula returns the position as a number.

Finally, starting from the first character, the LEFT function extracts the characters until the length of the first word – 1. We have to cut the last character because it’s the space.

Generic formula to get the first word

In the example, the formula looks like this:

=LEFT(B3,FIND(" ",B3)-1)

How to get the first word if the cell contains only one word? Error handling is important if you want to show that something went wrong. Using the formula mentioned above, we’ll get a #VALUE! Error. It is important to prevent formula errors use the IFERROR function.

In the example:

=IFERROR(LEFT(B3,FIND(" ",B3)-1),B3) or
=IFERROR(LEFT(B3,FIND(" ",B3)-1),”The cell contains one word”)

In this case, the formula returns the first word:

=IFERROR(LEFT(B3,FIND(" ",B3)-1),B3)
error handling using iferror

Explanation:

When an error occurs, the IFERROR expression returns with a user-specified message or the first word. Furthermore, it’s a smart way to handle the error: Add additional space to the cell value before running the FIND function.

=LEFT(B3,FIND(" ",B3&" ")-1)

Known limitations: The above-demonstrated formula works only with the first space.

Get the first word using a user-defined function

If you want to combine more than one built-in Excel into a formula, be careful; sometimes, it is not an easy task. In the example, we will use the ExtractNthWord user-defined function.

Syntax:

=Extract_Nth_Word(text, n)

Arguments:

  • text: the text string that we want to modify
  • n: the number of the word in the entire text

The solution looks like this:

=Extract_Nth_Word(B3,1)
custom udf text manipulation

If you have to clean data using Excel, we strongly recommend using our free excel add-ins. In addition, the productivity suite contains a custom function library.

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.