Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.
Often we face a situation where we need to get first and last word from a cell in excel.
For example, if you want to extract the first name from a cell where you have both first and last names or if you want to extract product name from the product description cell.
But in excel, there is no specific function to extract first and the last word from a text string. But, you can combine some text functions to get these words from a cell.
So, today, in this post, I will show you how to get first and the last word from a text string by using a combination of functions. And, please download this sample file from here to follow along.
Let’s understand this with an example.
In the below table, you have a list of names which includes the first and the last name. And, now from you this want to extract the first name which the first word in the cell.
And formula to get the first name will be:
This simply returns the first name which is the first word from the text.
In the above example, you have used a combination of SEARCH and LEFT.
Search Function: It can search for a text string in an another text string.
Left Function: It can extract a text from the left side.
This formula works in two different parts.
In the first part, SEARCH function finds the position of the space in the text and returns a number from which you have deducted one to get the position of the last character of the first word.
In the second part, using the number returned by SEARCH function left function extracts the first word from the cell.
Getting the last word from a text string can be tricky for you, but once you understand the entire formula it will much easier to use it in future.
=TRIM(RIGHT(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))),LEN(A2)))
This formula returns the last name from the cell which is the last word and works same even if you have more than two words in a cell.
Again, you need to break down this formula to understand how does it work.
In the first part, SUBSTITUTE function replaces every single space with the spaces equals to the length of the text.
And now, text become double in length with extra spaces and looks something like this.
After that, the RIGHT function will extracts characters from the right side equals to the original length of the text.
In the end, the TRIM function removes all the leading spaces from the text.
By using above formulas you can easily get the first and the last word from a text string. The formula for the first word is super easy and for the last word is bit tricky but you need to understand it once in starting.
I hope you found this formula tip useful.
Please share your views in the comment box, I would love to hear from you in the comment section.