Get the First Word from a Cell in Excel

Add to Favorites

How to use a formula to get the first word from a cell in Excel. This works for a single cell and an entire list or data set.

Sections:

Get the First Word in the Cell

Non-Space Separators

More Versatile Formula - Works when Only 1 Word is in a Cell or it is Empty

Notes

Get the First Word in the Cell

To do this, we use the LEFT() and FIND() functions.

Here is a formula that gets the first word from cell A1:

=LEFT(A1,FIND(" ",A1)-1)

(This formula assumes that your words are separated using spaces.)

The FIND function looks for the first space in the text and then returns that position back to the LEFT function. The LEFT function then uses that number to determine how many characters from the left of the cell to return. There is a -1 after the FIND function because that function finds the location of the first space and we want to get the count only up to the end of the first word; without the -1, the space after the first word will also be returned.

Non-Space Separators

If your words are separated using commas or dashes or anything that isn't a space, you need to use a formula like this:

=LEFT(A1,FIND("-",A1)-1)

Or

=LEFT(A1,FIND(",",A1)-1)

Change the character between the quotation marks to whatever separates your words.

More Versatile Formula - Works when Only 1 Word is in a Cell or it is Empty

When you copy any of the above formulas down a list of values, an error will be returned if any of the values contains a single word or no word at all. To get around this, we need to use the IFERROR function.

Here is the updated formula:

=IFERROR(LEFT(A3,FIND(" ",A3)-1),A3)

Result:

If there was no IFERROR function wrapped around everything, an error would be returned. However, now, if an error occurs, the IFERROR function returns the value of the original cell; if there is one word in that cell, it will now be returned; if the cell is empty, an empty cell will be returned.

This is a more versatile formula and is the one you should use in most cases.

Read more about the IFERROR function and suppressing errors in Excel.

Notes

Text manipulation in Excel seems impossible to learn and understand at first but I promise you that it is not that bad. You need to make sure that you understand each part of the formulas, each function, and you need to practice using them. Once you are comfortable using LEFT(), RIGHT(), MID(), FIND(), and LEN() you will have the tools you need to be the master of text manipulation in Excel.

Make sure to download the attached file so you can work with the above examples in Excel.


Downloadable Files: Excel File

Similar Content on TeachExcel
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This free Excel UDF (user defined function) returns the first word from a cell in Exce...
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the for...
SMALL Function - Get the Lowest Values from a List in Excel
Tutorial: Return the smallest values from a list, or any of the smallest values, in Excel. This inc...
LARGE Function - Get the Highest Values from a List in Excel
Tutorial: Return the largest value from a list, or any of the top values, in Excel. This method use...
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a...