Often we face a situation where we need to get first and last word from a cell/text string in Excel.

Let’s say...

...you want to extract the first name from...

...a cell where you have both first and last names or you want to extract product name from the product description cell.

We do need a function which can extract this.

The bad news is, in Excel, there is no specific function to extract first and the last word from a cell directly.

But the good news is you can combine functions and create a formula to get these words.

Today in this post, I’d like to share with you how to get first and the last word from a text string by using a formula.

Download this sample file from here to follow along and now let’s get down to business.

## 1. Extract First Word

Extracting the first word from a text string is much easier than extracting the last word.

For this, we can create a formula by combining two different text functions, that’s SEARCH and LEFT.

Let’s understand this with an example.

In the below table, we have a list of names which includes the first and the last name. And now from this, we need to extract the first name which the first word in the cell.

And the formula to get the first name from above column is:

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

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

### How it works

In the above example, we have used a combination of SEARCH (It can search for a text string in another text string) and LEFT (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.

## 2. Extract 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 formula will be:

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 it works

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.

The text becomes 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.

## Sample File

Download this sample file from here to learn more

## 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 tip helpful.

Now tell me one thing.

**Do you have any other method to get first and last from a string?**

Please share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.

**Must Read Excel Tutorials**

Nice formula tip,

Thanks

TRIM(RIGHT(SUBSTITUTE(TRIM(A1),” “,REPT(” “,255)),255))

Changes all spaces into string of 255 blanks, then trims to the left leaving the last word intact.

Nice. If only everyone had a single word surname. Nothing like a van Beek or van der Wettering to create formulaic issues. I did this many years ago; from memory there were various IF’s and OR’s involved.

We can also do this through Flash Fill (Clt + E)

I only get an error about too few arguments on both of Adrians suggestions. Running Excel 2016 latest version.

Suppose your text is in cell A1. Then to find the last word in B1 write:

= LEN(TRIM(A1)) – LEN(SUBSTITUTE(A1, ” “, “”,))

This give as result the number of spaces in A1

Then in B2 write:

= SUBSTITUTE(A1,” “, “*”,B1)

This replaces the last space of A1 by an asterisk

In B3 write:

=FIND(“*”, B2)

That finds the position of “*”

In B4 write:

= RIGHT(A1, LEN(A1) – B3)

If you want, you could indent all this formulas in one (a little more complex, but it’s possible)

Sincerely yours,

Carlos M.

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

Adrian

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

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

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.

Please, share it with us.

Thanks’