Quick, clean, and to the point

Get first word

Excel formula: Get first word
Generic formula 
=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)

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.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.