Delete alpha characters from cell (2003)

Topic

New Reply

I wonder if someone could help me with this task: I have cells that contain, for example, Smith, John. I would like the cell to contain just the last name only; no apostrophe or first name. Is there a way to do this via a formula?

Thanks in advance!
Deb

Reply | Quote

Replies

Edited by HansV to correct error in formula (thanks to Franciz for pointing it out!)

You can use a formula to extract the last name to another cell, for example the cell next to the one with the full name.
If you have a full name in cell A2, the following formula in B2 will extract the last name:

=LEFT(A2,FIND(",",A2)-1)

You can fill this formula down.
If you wish, you can copy the range with formulas, paste special as values, then delete the original data.

If you want to replace the names within the cells themselves, without using intermediate formulas, you’d have to use VBA code.

Reply | Quote

Hans,
you means to say this? =LEFT(A2,FIND(“,”,A2)-1)

Deb, pls note that this formula will return all characters on the left side of a ,
in this case Smith is returned based on your example. If your full name include other characters beside
a , as separator, this will not work properly

If the last name is the other way round, try this
=RIGHT(A2,LEN(A2)-FIND(“,”,A2)) which will return all characters after a , eg Smith in John, Smith

HTH

cheers, francis

Reply | Quote

Thank you, I did mean =LEFT(A2,FIND(“,”,A2)-1)

Reply | Quote

Thank you Hans and Franciz. I delayed in posting back as I was trying to get the original formula to work (I didn’t see Franciz’ post until just now!). The benefit of the tiny error in the original formula is that it forced me to do a bit reading about this formula which led to a better understanding of how it works!

All is well and thank you both for the instruction!

Reply | Quote