Flipping a last name comma first name in the same cell

[Tina Marie]

Hello ...

Does anyone know how to flip last name and first name

e.g. Robinson, Tina to Tina Robinson in the same cell

 

[Mike H]

Hi,

If it must be in the same cell then that's a macro so post back but if an
adjacent cell will do try this for a name in a1

=MID(A1,SEARCH(" ",A1)+1,LEN(A1))&" "&LEFT(A1,SEARCH(",",A1)-1)

Mike

 

[Conan Kelly]

Tina Marie,

Then you could copy from the results of the formula Mike H provided, and
paste special as values into the original cell, then delete the formula.

HTH,

Conan

 

[Raymie Younkin]

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

Where C3 is the cell with the name formatted as

Smith, John

 

[Don Guillett]

Sub flipnameinsamecell()
For Each c In range("a2:a22")'Selection
x = InStr(c, ",")
c.Value = Right(c, Len(c) - x) & " " & Left(c, x - 1)
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message news:[email protected]...

 

[Catherine Damiani]

say i have John Smith in A1 and want it to be Smith John in A2, what would the formula look like that would be in A2?

thank you!!!

 

[Per Jessen]

Hi

This will not work if A1 contains a middlename.

=RIGHT(A1,SEARCH(" ",A1)) & " " & LEFT(A1,SEARCH(" ",A1)-1)

Regards,
Per

"Catherine Damiani" skrev i meddelelsen news:[email protected]...

 

[Ashish Mathur]

Hi,

If there is no middle name, then select column A and then Data > Text to
columns. While on delimited, click next and on the next screen, uncheck all
boxes except spaces. Click on finish. Now in a spare column, enter B1&"
"&A1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

in message news:[email protected]...

 

[Rick Rothstein]

This will work as long as there is no middle initial or middle name and as
long as the first name is never made up of two names (such as Mary Anne)...

=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))