Get the First Word from a Cell in Excel

Add to Favorites
Author:

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

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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 fo...
Formulas to Remove First or Last Character from a Cell in Excel
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Formula to Get the Last Value from a List in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...
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 grea...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course