Convert proper name format.

[Doug24]

I would like to change names formatted like this:

John Smith

to this:

Smith J

Thanks for any help,
Doug

 

[Joe4]

If the format is also exactly two words separated by a single space, then you could use this formula (for an entry in cell A1):

=RIGHT(A1,LEN(A1)-FIND(" ",A1)) & " " & LEFT(A1,1)

If you can have more words, then it gets real tricky, because how can you tell how many names go with first name and how many go with last?
For example, you could have a two first name, like:
Billy Ray Cyrus
oe you could have a two word last name, like:
Martin Van Buren

So, you really cannot develop a "hard-and-fast" rule that will handle all these possibilities perfectly.

 

[Doug24]

Thanks Joe. That's exactly what I need. I do see what you're saying about more than two word names.
I'm lucky that any double word last names are entered without the space so Van Buren is entered as
VanBuren and I don't have any double word first names.

Thanks again for your help.
Doug

If you can have more words, then it gets real tricky, because how can you tell how many names go with first name and how many go with last?
For example, you could have a two first name, like:
Billy Ray Cyrus
oe you could have a two word last name, like:
Martin Van Buren

So, you really cannot develop a "hard-and-fast" rule that will handle all these possibilities perfectly.

 

[Joe4]

You are welcome!

I'm lucky that any double word last names are entered without the space so Van Buren is entered as
VanBuren and I don't have any double word first names.

Well, that certainly makes things easier! Its nice when things work out like that.
Many times, we are trying to deal with a lot of messy data.

 

[jtakw]

Hi,

Here's another formula to consider:

Book1
	A	B
1	John Smith	Smith J
Sheet182

Cell Formulas
Range	Formula
B1	=MID(A1,FIND(" ",A1)+1,255)&" "&LEFT(A1,1)