# How to Extract the First Four Words

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

Formula:

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," ","")))<4, A1, LEFT(A1, FIND(" ",A1, FIND(" ",A1, FIND(" ",A1, FIND(" ",A1)+1)+1)+1)-1))

Example:

To extract the first four words from the text string "How to Extract the First Four Words".

The result returns the first four words "How to Extract the".

Explanations:

Step 1: To find the location of the first space

Step 2: To find the location of the second space

Step 3: To find the location of the third space

Step 4: To find the location of the fourth space

Step 5: Pick up the letters before the fourth space

When text string has less than four words, the formula "=LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1, FIND(" ",A1)+1)+1)+1)-1)" returns #VALUE! error. To avoid this error, you need to combine with IF Function.

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

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

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

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