To extract the first two words in the text string, you need to find the relative location of the second 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," ","")))<2, A1, LEFT(A1,FIND(" ",A1, FIND(" ",A1)+1)-1))
Example:
To extract the first two words from the text string "How to Extract the First Two Words".
The result returns the first two word "How to".
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: Pick up the letters before the second space
| Formula | =LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1) |
|---|---|
| Result | How to |
However, when text string has only one or two words, the formula "=LEFT(A1, FIND(" ", A1, FIND(" ", A1)+1)-1)" returns #VALUE! error. To avoid this error, you need to combine with IF Function.
=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 2, use IF Function to return the text string itself.
Thank you! This works perfectly.