Site icon ExcelNotes

How to Extract the First Three Words

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

Step 2: To find the location of the second space

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

Step 3: To find the location of the third space

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

Step 4: Pick up the letters before the third space

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

Download Example

Exit mobile version