Retrive the First Word from a Text String

Formula to Find the first word in Excel with the Find and Left Functions
To retrieve the first word from a cell use the following formula:
=left(b5,1,find(" ",b5)-1)

We use two functions. The LEFT Function returns the first n number of characters. The FIND Function finds the first blank cell. We set n (in the LEFT Function) equal to the result of the FIND Function -1 to return the first word. Let’s examine it more in depth below:

The FIND Function:

Use the FIND Formula to find the first blank character in the cell.
=find(" ",b5)
Result: 9

This will tell the LEFT Function when to stop.

The LEFT Function

Use the result of the Find formula (-1) to tell the Left Function to return the first 8 characters (9-1) .
Result: Retrieve

Formula with Excel Left Function and Find Functions are used to get the first word in a string of text

The IFERROR Function

If there is only one word in the text string, the previous formulas will return the error: #VALUE!. We can use Excel’s IFERROR Function to prevent this error. The IFERROR Function checks if the original formula results in an error. If not, then it returns the original result. If yes, then it does something else.
Add the IFERROR Function to the formula to prevent errors when finding the first word

Notice in the first example, the formula results in the error: #VALUE! because no blank characters were found in the text string. In the second example, we apply the IFERROR Function to the formula, indicating if no blank characters are found, then return the entire text string.
=IFERROR(LEFT(B6,FIND(" ",B6)-1),B6)

Now you know how to use an Excel formula to return the first word in a cell. What if you want to return the second word in a cell?

Find the Second Word:

The second word will be in between the first and the second space within the string of text. We already have the location of the first blank character. Let’s find the second blank character. You do this by telling the FIND Function to begin the search after the first blank character.

Formula to find the second word in a text string with the FIND and MID Functions.
Result: Second

For more information visit Microsoft’s Website.

Return to the Excel Formula Examples page