Site icon ExcelNotes

How to Extract the First Two Words

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)
Result4

Step 2: To find the location of the second space

Formula=FIND(" ",A1,FIND(" ",A1)+1)
Result7

Step 3: Pick up the letters before the second space

Formula=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)
ResultHow 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.

Download Example

Exit mobile version