Excel formula: Get first word

Generic formula

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

Summary

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)

Explanation

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.

Author

Dave Bruns

Related formulas

Extract nth word from text string

At the core, this formula takes a text string with spaces, and "floods" it with additional spaces by replacing each space with a number of spaces using SUBSTITUTE and REPT . The number of spaces used is based on the overall length of the original...

Extract word that begins with specific character

Starting from the inside out, the MID function is used to extract all text after "@": MID ( B5 , FIND ( "@" , B5 ), LEN ( B5 )) The FIND function provides the starting point, and for total characters to extract, we just use LEN on the...

Split text string at specific character

The first formula uses the FIND function to locate the underscore(_) in the text, then we subtract 1 to move back to the "character before the special character". FIND ( "_" , B5 ) - 1 In this example , FIND returns 7, so we end up with 6...

Abbreviate names or words

Working from the inside out, the MID function is used to cast the string into an array of individual letters: MID ( B5 , ROW ( INDIRECT ( "1:" & LEN ( B5 ))), 1 ) In this part of the formula, MID , ROW , INDIRECT , and LEN are used to...

Split text with delimiter

The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get rid of the extra space. In this...

Related functions

Excel LEFT Function

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT("apple",3) returns "app".

Excel FIND Function

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.

Excel ISERROR Function

The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or...

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.