Get the first words of Excel cells

The first word of a cell, everything before the first space, often draws the attention of anyone who works with text data. You need to treat it as a separate unit: capitalize it, put it into a separate cell, keep only it in the original cell and so on.

Besides the first word, it’s often important to get the second one, the third one and so on. And if you need to remove the first words from cells after extracting them, that’s a completely different operation with its own article.

Capitalize the first word of a cell

Capitalizing the first word is easy if there are no other characters before it. But if the cell starts with quotes, brackets, a dash or other punctuation, the solution is much more complex than just taking the first character of a cell and making it uppercase. That’s why I covered this task in a separate article: how to make the first letter in a cell uppercase.

First word into another cell – Excel formula

If the first word means the characters before the first space, the formula is quite simple:

=LEFT(A1,SEARCH(" ",A1&" ")-1)

Here A1 is the cell with the word. The SEARCH function finds the position of the first space, and the LEFT function takes everything before it.

Did you notice the extra space added to the cell value with the ampersand (&)? It’s there for cells with a single word or no words at all. Without it, SEARCH finds no space and the formula returns an error.

The convenience of a formula is that when you fill it down the column, the first words update automatically as the data in the first column changes. In Excel 365, the same is shorter with TEXTBEFORE:

=TEXTBEFORE(A1&" "," ")

Keep only the first word in the cell

The simplest way is the built-in Find and Replace. Press Ctrl+H, type a space followed by an asterisk (“ *”) in the Find what box, and leave Replace with empty. The asterisk is a wildcard that means “any number of any characters”, so everything after the first space disappears.

Be careful: this changes all cells on the sheet unless you select a range first, and it can’t be undone after saving. The !SEMTools option is safer and works only on the selection: EXTRACT → Extract words → by position.

Get the first 2, 3 or N words

The more words you want to extract from a cell, the harder it gets. Google Sheets has the great SPLIT function, which splits a cell into words so you can take each by its index. Older Excel versions have nothing like it.

But there are alternatives. For example, to get the first two words, one option looks like this:

=IFERROR(LEFT(A1,SEARCH(UNICHAR(23456),SUBSTITUTE(A1&" "," ",UNICHAR(23456),2))-1),A1)

The formula relies on special properties of SUBSTITUTE and UNICHAR:

• the SUBSTITUTE function can replace exactly the N-th occurrence of a substring;
• the UNICHAR function returns a character so rare that it’s almost certainly not in the cell.

Then SEARCH finds the position of this character, and LEFT takes everything before it (1 is subtracted from the position). If the cell has fewer words than requested, there is nothing to replace, and IFERROR returns the whole cell.

It’s a good reliable formula with its pros and cons. The advantage is that it’s easy to adapt for 3, 4 or more words: just change the last argument of SUBSTITUTE, 2, to the number you need. The drawback is that UNICHAR works only in Excel 2013 and later. Here is an equivalent for older versions with the CHAR function:

=IFERROR(LEFT(A1,SEARCH(CHAR(9),SUBSTITUTE(A1&" "," ",CHAR(9),2))-1),A1)

The formula inserts a tab character, which is also very unlikely to be in the text already.

Get the first N words with an Excel 365 formula

In Excel 365, TEXTSPLIT, SEQUENCE and TEXTJOIN make this much simpler. Replace N with the number of words:

=TEXTJOIN(" ",TRUE,INDEX(TEXTSPLIT(A1," "),SEQUENCE(N)))

• TEXTSPLIT splits the text into separate words;
• SEQUENCE(N) generates the numbers 1 to N;
• INDEX takes the first N words;
• TEXTJOIN joins them back with spaces.

An even shorter option is TEXTBEFORE with the occurrence number. IFERROR returns the whole cell if it has fewer words than N:

=IFERROR(TEXTBEFORE(A1," ",N),A1)

Get the second, third or N-th word

Sometimes you need not the first N words, but one specific word, for example the second. The classic trick replaces every space with 100 spaces using REPT, takes the 100-character chunk where the word is with MID, and removes the extra spaces with TRIM. For the second word:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),(2-1)*100+1,100))

In Excel 365, it’s simply:

=INDEX(TEXTSPLIT(A1," "),2)

Extract first N words in 2 clicks

Knowing Excel functions helps a lot, and for enthusiasts I have a whole Excel functions reference. But building complex formulas takes time, and so does keeping a huge file with examples somewhere on your disk.

If you value your time, the procedures of my !SEMTools add-in will help. One of them takes the first N words in all selected cells and either puts them into the next column or keeps them in the original cells. A small checkbox controls the output mode. No formulas to remember, type in or fill down:

Why the first word isn’t extracted: common mistakes

• The formula returns #VALUE!. The cell is empty or has no spaces, and the extra space after A1 was forgotten. Check that the formula contains A1&" ".
• The first word is empty. The cell starts with a space. Remove extra spaces first or wrap A1 in TRIM.
• Punctuation stays with the word: “Hello,” instead of “Hello”. The formula splits by spaces only. Remove punctuation first if you need clean words.

Summary: which way to get the first words

I hope you found what you were looking for on this page and were able to get the first words from your cells. For the first word, LEFT + SEARCH is enough, for N words use the SUBSTITUTE trick or TEXTBEFORE in Excel 365. For whole columns, !SEMTools does it in a couple of clicks, no syntax required.

You may also be interested in related operations in the extract words section and in counting words in cells.