Table of Contents
To extract the first two words from a cell in Google Sheets, you can use the LEFT function combined with the FIND function. First, use the FIND function to locate the position of the first space in the cell. Then, use the LEFT function to extract the first word based on the position found. Next, use the FIND function again to locate the position of the second space in the cell. Finally, use the LEFT function once more to extract the second word based on the position found. This process allows you to extract the first two words from a cell in Google Sheets.
You can use the following syntax to extract the first two words from a cell in Google Sheets:
=LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",2))-1)
This particular formula will extract the first two words from the string in cell A2.
The following example shows how to use this syntax in practice.
Example: Extract First Two Words from Cell in Google Sheets
Suppose we have the following list of phrases in Google Sheets:
Suppose that we would like to extract the first two words from each cell.
We can type the following formula into cell B2 to do so:
=LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",2))-1)
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the first two words from each corresponding string in column A.
Note that the formula works no matter how long the string or phrase is in any given cell.
How This Formula Works
Recall the formula that we used to extract the first two words from cell A2:
=LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",2))-1)
First, we use SUBSTITUTE(A2,” “,“*”,2) to replace the second space in the string in cell A2 with an asterisk (*).
Next, we use the FIND function to find the position of the asterisk in the string.
This represents the position just after the second word in the string.
Then, we subtract one from this location.
Lastly, we use the LEFT function to extract all text to the left of this final location.
The end result is that we’re able to extract the first two words from the string in cell A2.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets: