Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Get the First Word from a Cell in Excel
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
More Versatile Formula - Works when Only 1 Word is in a Cell or it is Empty
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.
Question? Ask it in our Excel Forum
Macro: This free Excel UDF (user defined function) returns the first word from a cell in Exce...
Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the fo...
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a grea...