Invert first and last names

[lcarlson]

Quick questions: What vba code is used with command button to invert first and last names in an excel spreadsheet cell?

Larry

 

[Norie]

You don't really need VBA code, it could be done with worksheet functions (MID, LEFT, FIND etc)

The actual code/functions to use would depend on the format the name is stored in.

How are the first name and last name seperated?

 

[lcarlson]

Thanks for the reply. Yes, I am sure that I can just use a formula using the terms MID, LEFT, TRIM etc. However, there's the rub. I am not able to figure out the exact formula. If you could relate the exact formula I would be very grateful.


Larry

 

[lcarlson]

Sorry, the format that I want is
to reverse Joe Brown to Brown, Joe

 

[Zack Barresse]

Try this ...

As originally provided by Nathan Oliver

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr","III","IV","V","VI"},"")))),1))))&", "&SUBSTITUTE(A1," " &RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Jr","III","IV","V","VI"},"")))),1)))),"")

This will handle most any name you can throw at it. Found here.

 

[Zack Barresse]

Fwiw, Chip Pearson also has a great example for a situation like this ...

http://www.cpearson.com/excel/FirstLast.htm


HTH

 

[lcarlson]

Zack,

That's great. Just what I wanted.

One minor thing. Is it possible to put this formula into an If
statement (such as =IF(ISBLANK(A1), "", formula for inverting names).

In actual practice, I get a #Value message whenever there is a blank
in the list of names in column A1.

A minor refinement, but I was just curious as to whether it was possible.
I tried for a while, with no luck.

Larry

 

[Norie]

Larry

I know a solution has already been provided but a simpler one might be just something like this:

=IF(ISBLANK(A1),"",MID(A1,FIND(" ",A1)+1,LEN(A1))&" ,"&LEFT(A1,FIND(" ",A1)-1))

This might also deal with your 2nd question.

 

[Zack Barresse]

If you wanted to use the formula I posted, you could have it in an intermediary column, possibly hidden (your discrepancy) and use it something like this ...

Book24
	A	B	C	D
1	JohnDoeSr	DoeSr,John	DoeSr,John
2		#VALUE!
Sheet1

Column B above would be hidden, then the formula in column C is ..

=IF(ISERR(B1),"",B1)
copy down as needed.

I also added a "Sr" into the function, as it didn't have it in the prior posting. Which would make it ...

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Sr","Jr","III","IV","V","VI"},"")))),1))))&", "&SUBSTITUTE(A1," " &RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Sr","Jr","III","IV","V","VI"},"")))),1)))),"")

You could also break this formula up into more than one cell in other ways, but the error check is my personal preference.

If you do not need the extreme flexibility of this solution, maybe the one Norie posted would suffice for you. Remember, if you only drive a Volvo, there's no (practical) need to gear it up as a Rolls Royce. Unless you are from Texas and like everything big. (j/k)

 

[Scott Huish]

How about:

=IF(ISBLANK(A1),"",REPLACE(A1,1,FIND(" ",A1),"") & ", " & LEFT(A1,FIND(" ",A1)-1))

Book2
	A	B	C	D
1	JohnDoeSr.	DoeSr.,John
2	JoeBrown	Brown,Joe
3
Sheet1