Get first word

An excel formula to get first word

Related Functions

Sponsored Link

 Get first word
=LEFT(a1,FIND(" ",a1)-1)

If you need to extract the first word from some text you can use a formula that uses the FIND and LEFT functions. From the example, the formula looks like this:

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

How this formula works

FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars.

The LEFT function then extracts characters starting at the the left side of the text, up to (position - 1).

Handling one word

If a cell contains only one word, this formula returns an error. One way to fix this problem is to wrap the original formula in the IFERROR function like so:

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

Translated: if an error occurs, return the original text.

Another simple way to handle the error is to append a space to the cell value before running FIND:

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

This ensures that FIND will always find at least one space, and will therefore not throw an error. When a cell contains more than one word, there is no impact because the formula works only with the first space.

Sponsored Link

0 votes. 0 / 5