Today, I encountered a situation where I had to separate the full names of our customers into the first and last names. The names are comma separated, so I was looking for an Excel formula that can help me extract the last name before the comma and the first name after the comma.

For example, you have the following text in a cell “A1″ and you’d like to extract the text before the comma (the last name):

“Doe, John”

To extract the last name before the comma “Doe” from A1, use one of the following formula:

`=LEFT(A1,(FIND(",",A1,1)-1))`

The result: Doe

To extract the first name after the comma from A1, use one of the following formula:

`=MID(A1,FIND(",",A1)+2,256)`

The result: John

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

## 34 Comments

Hi! Thanks for this formula. I do have a question about using a similar formula. Maybe someone can help me out here. I have a list of names which is separated by comma’s. I want to extract

ONLY the last name in the list. To identify my problem I use a list with the same last names in the list.Column A (each line represents a different row, A1:A3)

McPherson DB, Bennett J.

Relay RM, Bennett J.

Worth TH, Bennett J.

I have tried using the following formula in Column B to extract the last name only:

RIGHT(A1;(FIND(“,”;A1)-1))

RIGHT(A2;(FIND(“,”;A2)-1))

RIGHT(A3;(FIND(“,”;A3)-1))

This is what the formula returns in the 3 different rows:

ennett J.

, Bennett J.

Bennett J.

The problem here is that in the first row it left out the ‘B’. In the second row, also the ‘, ‘ are returned. Only row 3 returns what I want and was expecting to return.

Can someone explain me why using this formula gives me this inconsistency?

Thanks!Hi John, if you’re looking to extract the string AFTER the comma, you can use this formula:

`=MID(A1,FIND(",",A1)+2,256)`

and if on top of the formula, I want to put an “if” condition to return the value of the cell if it doesn’t find any comma, how would that be?

thanks in advance

Andre

Hi Andre! That’s easy! You can just use this formula:

`IF(ISNUMBER(SEARCH(",",A1)),LEFT(A1,(FIND(",",A1,1)-1)),A1)`

(if you want to extract the text BEFORE the comma) or`IF(ISNUMBER(SEARCH(",",A1)),MID(A1,FIND(",",A1)+2,256),A1)`

(if you want to extract the text AFTER the comma).hi need help,

how do i extract the word BLUE_ELEPHANT from the string below :

ANIMAL::BLUE_ELEPHANT_BABY_MAX_0000

thanks alot

Hi Carmen, are you always looking to extract the first two words after the colon marks?

How would I extract the last name from a cell that looks like this: 2 BAZZARI, KHALDOUN F.

Also, how would I create a formula that also extracts the last name from a cell that has entered double digits like: 21 BAZZARI, KHALDOUN

Hi Taylor, I understand that you have a unique ID before the last name and you want to extract the last name that sits between the unique ID and the comma. Is that correct? If that’s the case, try the formula below:

=MID(LEFT(A1,(FIND(“,”,A1,1)-1)),FIND(” “,LEFT(A1,(FIND(“,”,A1,1)-1)))+1,256)

Hope this helps!

I need this formula, but when i enter it it says I have too many arguments

Hi Nikki, there seems to be something wrong with your formula. Do you mind copying and pasting it here, so I can take a look and give you feedback on what could be wrong?

THIS WORKED FOR ME! JUST HAD TO CHANGE THE COMMA TO A SEMICOLON.

Yes, you can definitely easily change the comma to a semi-colon or any other character

this doesnt work at all, it says “Find” is not defined, and does not read it. also can you do right function until comma with cells(#,#) instead of using a range?

Hi Timothy, I am sorry to hear that the formula did not work for you! Feel free to add a link to the file if you want me to help debug your formula! I’ve used this formula a number of times and it has worked for me every time!

works perfect for me, thanks alot

Glad it worked for you, Asif!

Awesome! Thank you very much!

Hey…

Thanks a lot …

I was searching for this formula for long time. This saves a lot of time and embarrassment while working on Excel.

Thanks,

Khwaja

Thanks for sharing this! It just saved me literally HOURS of manual data entry between excel and access.

This was very helpful….it worked perfectly. In my case, I wanted everything before the “@”. I simply replaced the comma value “,” with this value “@”. Perfecto. Thanks soooooo much.

That’s right Kory! You can easily replace the comma with any other symbol. Glad you figured out how to easily get that done!

Thanks for these, it helps me a lot and saved time as well.

But same as shirleyshirley here, i want to understand the formula so it will be easy for me to remember, if i know the logic of the formula then i will not forget it. I cannot get the logic for 1 after A1, and what is -1 for? Also in the second formula, why it is MID, i thought it would be RIGHT? What is +2 for also? why there is no ,1 or ,2 after A1?

Thanks a lot again.

Hi Karen,

My situation is one step further, where I have a cell which contains Surname, Firstname pseud: xxxxxx. I only want “Surname, Firstname”, and I don’t want anything that comes after that. However the number of characters in the first name will therefore vary, so how do I change the 256 character search to instead search up to the next ” ” (space) instead?

With kindest thanks for you help so far!

Lucy

Hi Lucy,

Hope you may have got the solution. If not, following is one solution!

=LEFT(A1, FIND(” “, A1, FIND(” “,A1,1)+1)-1)

Assuming that A1 holds “Funny Man From India”

The formula reads for first blank space and adds one to that. Then search for the next one!

I love this! It works perfectly and saved me hours of time

Hi Danielle! So happy that I was able to help save you time

thnksssss for the help karen

You are very welcome, Sammer!

thank you for this quick tip – it worked great

You’re welcome! Glad I was able to help you save time

can you explain what those codes mean? thank you

i.e. can you tell me what “,” means? why did you place 3 values separated by comma within ()? –>(x,x,x)=(“,”,A1). how does it work? what’s the 256 for? thank you!

Hi Shirley! The “,” just means that the formula should find the comma in the string. The “256” indicates the number of characters that you wish to extract after the comma. You can put any number here, as long as it’s high enough to cover the number of characters after the comma. The higher the number, the better

Hi Shirley! You can use these functions if you encounter a situation where you have to separate comma-separated strings (for example, if you want to separate the last name from the first name and there’s a comma in between the two).