How to Extract the First Word

To extract the first word in the text string, you need to find the relative location of the space, then use Left Function.


Copy the formula and replace "A1" with the cell name that contains the text you would like to extract.

=IF(LEN(A1)- LEN(SUBSTITUTE(A1," ",""))<1, A1, LEFT(A1,FIND(" ",A1)-1))


To extract the first word from the text string "How to extract the first Word".

The result returns the first word "How".


Step 1: To find the location of the first space

Formula=FIND(" ",A1)

Step 2: Pick up the letters before the first space

Formula=LEFT(A1, FIND(" ",A1)-1)

However, when text string has no space, the formula "=LEFT(A1, FIND(" ",A1)-1)" returns #VALUE! error. To avoid this, you need to combine with IF Function.

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))<1, A1, LEFT(A1,FIND(" ",A1)-1))

=SUBSTITUTE(A1," ","") to replace spaces in the text string;

=LEN(SUBSTITUTE(A1," ","")) to count the length of the text string when spaces are replaced;

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) to count the number of spaces in the text string.

When the number of spaces is less than 1 (meaning a word), use IF function to return the text string itself.

Another method is to use ISERROR Function to check whether the text string contains spaces:

=IF(ISERROR(FIND(" ",A1))=TRUE, A1, LEFT(A1,FIND(" ",A1)-1))

Download Example

Leave a Reply