The first word in an Excel cell is often key for those working with text. You might need to highlight it separately – to capitalize, move to another cell, or keep it as is.
You may also need the second word, and so on.
Removing the first words after extracting them is a different process, detailed in another article.
Output the first word to another cell – formula
To get the characters before the first space in a cell, use:
=LEFT(A1;SEARCH(" ";A1&" ")-1)Here, A1 is your target cell.
Notice the extra space added after A1 with an ampersand (&)? It’s for cells with only one word or none. Without this space, you’d get an error in these cases.
This formula helps avoid errors. Stretching it across a column automatically updates first words in adjacent cells when the original data changes.
Leave only the first word in the cell
The easiest way to do this in Excel is with the Find and Replace procedure. You can call the procedure with the hotkey Ctrl + H, enter a space with an asterisk in the first window (see wildcards in Excel), and leave the second empty as is.
Use “Find and Replace” to replace all words with a space after the first, with a blank.
Take the first 2/3/N words of the cell
Extracting more words gets trickier. Google Sheets has a SPLIT function for this, but Excel doesn’t.
You can use this formula for the first two words:
=LEFT(A1;SEARCH(UNICHAR(23456);SUBSTITUTE(A1&" ";" ";UNICHAR(23456);2))-1)It uses SUBSTITUTE and UNICHAR:
- SUBSTITUTE replaces the N-th space with a unique character (here UNICHAR(23456)).
- SEARCH finds this unique character’s position, and LEFT takes everything before it.
Modify the formula for more words by changing the SUBSTITUTE function’s argument.
The downside: UNICHAR works only in Excel 2013 and later. For earlier versions, use CHAR:
=LEFT(A1;SEARCH(CHAR(9);SUBSTITUTE(A1&" ";" ";CHAR(9);2))-1)This adds a tab character (also likely not present in your original data).
Procedures to extract the first N words
Knowing Excel functions and formulas helps a lot at work, and for enthusiasts that love learning formulas, I have a whole Handbook of Excel Functions. However, it can be costly to spend time on complex constructions, as well as to keep somewhere on disk a huge file with examples of their use.
Those who are time-conscious will find my add-in procedures useful, including one that allows you to take the first N words in all selected cells. And either paste into the adjacent column or leave them in their original cells. A small checkbox in the add-in is responsible for the output mode:
