Carlos needs to combine words in a first name and a last name column

Home ➜ Excel Formulas ➜ How to Separate Names in Excel – (First & Last Name)

Often we face a situation where we need to separate names (get the 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 the product name from the product description cell.

We do need a function that can extract this.

The bad news is, in Excel, there is no specific function to extract the 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 the separate names using a formula

sample file.xlsx

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 is the first word in the cell.

And the formula to get the first name from the 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, the 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 the SEARCH function, the 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 be much easier to use it in the future.

So, to extract the last word from a cell you need to combine RIGHT, SUBSTITUTE, and REPT. And the formula 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 the 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, the SUBSTITUTE function replaces every single space with spaces equal 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 extract characters from the right side equal to the original length of the text.

In the end, the TRIM function removes all the leading spaces from the text.

sample file.xlsx

Conclusion

By using the 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 a bit tricky but you need to understand it once it starts.

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.

🔙 Excel Formulas

More Formulas