Retrive the First Word from a Text String
To retrieve the first word from a cell use the following formula:
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.
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) .
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.
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.
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.
For more information visit Microsoft’s Website.
Return to the Excel Formula Examples page