To extract the first three words in the text string, you need to find the relative location of the third 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," ","")))<3, A1, LEFT(A1,FIND(" ",A1, FIND(" ",A1, FIND(" ",A1)+1)+1)-1))
Example:
To extract the first three words from the text string "How to Extract the First Three Words".
The result returns the first three words "How to Extract".
Explanations:
Step 1: To find the location of the first space
| Formula | =FIND(" ",A1) |
|---|---|
| Result | 4 |
Step 2: To find the location of the second space
| Formula | =FIND(" ",A1,FIND(" ",A1)+1) |
|---|---|
| Result | 7 |
Step 3: To find the location of the third space
| Formula | =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1) |
|---|---|
| Result | 15 |
Step 4: Pick up the letters before the third space
| Formula | =LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)-1) |
|---|---|
| Result | How to Extract |
However, when text string has less than three words, the formula "=LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)-1)" returns #VALUE! error. To avoid this error, you need to combine with IF Function.
=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<3, A1, LEFT(A1, FIND(" ",A1, FIND(" ",A1, FIND(" ",A1)+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.
When the number of spaces is less than 3, use IF Function to return the text string itself.
