Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth \$20, Absolutely Free

Get First and Last Word

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.

Get First Word

Extracting the first word from a text string is much easier than extracting the last word. For this, you need to combine two different text functions: SEARCH and LEFT.

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:

=LEFT(A2,SEARCH(” “,A2)-1)

This simply returns the first name which is the first word from the text.

How Does it Work?

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.

Get Last Word

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.

So, to extract the last word from a cell you need to combine RIGHT, SUBSTITUTE and REPT. And the formulas will be:

=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.

How Does it Work?

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.

Conclusion

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.

• Bruno Bowald

Cool solution Puneet!
I have not had problems extracting the last names before, but
I’ve never seen it done with YOUR function-combination.
There’s always another way to do things in Excel – thanks

• Puneet Gogia

Yup.

Well, after writing, this I have got a super cool solution to do it without using formulas and even without using VBA.

That’s text to column, but not the normal way we always do.

• mma173

Thanks’

• asa

This formula is a bit simpler and extracts the last name =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))

Hi Puneet

I prefer the slightly more general case version of:

=IF(ISERR(FIND(” “,A1)),A1,RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))-B1+1))))

as I am often trying to get the last one or two words of an address. I can just go down column B and change any numbers that don’t make sense eg New York requires a 2, and Rio de Janeiro would require a 3. If I only want the last word I just delete “-B1+1”.

It also has the advantage of working on very long strings

Thank you for our daily lessons. I have learned a lot from you