You can use the following formula in Excel to extract the first 3 words from a cell:
=TEXTBEFORE(A2, " ", 3)
This particular formula extracts the first 3 words from cell A2.
The following example shows how to use this formula in practice.
Example: Extract First 3 Words from Cell in Excel
Suppose we have the following column of phrases in Excel:
Suppose we would like to extract the first 3 words from each cell in column A.
We can type the following formula into cell B2 to do so:
=TEXTBEFORE(A2, " ", 3)
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the first 3 words from each cell in column A.
Note: You can replace the value 3 in the formula with any value you’d like to instead extract the first n words.
How This Formula Works
Recall the formula that we used to extract the first 3 words from each cell:
=TEXTBEFORE(A2, " ", 3)
This formula uses the TEXTBEFORE function, which uses the following basic syntax:
TEXTBEFORE(text, delimiter, [instance_num], …)
where:
- text: Text to search
- delimiter: Character or substring to extract text before
- instance_num (optional): Instance of delimiter before which to extract text (default is 1)
The first argument specifies that we want to search cell A2.
The second argument specifies that we want to extract text before a space.
The third argument specifies that we would like to extract the text before the third space.
By using this formula, we are able to extract the first 3 words, since exactly 3 words can occur before the third space is encountered.
Note: You can find the complete documentation for the TEXTBEFORE function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Extract First Letter of Each Word
Excel: How to Extract First Number from String
Excel: How to Extract Last Word from Cell
