How do I rearrange the order of words in a cell, consistently?

[smart analyst]

Hi all,

I am trying to rearrange a mass list of names in a cell. The good thing is that all of the names are formatted similarly that is:

"Last-name, first-name"

Each cell starts with the last name and then has a comma, and then includes their first name.


So for John Smith (Smith, John) - is there any way I can put in a formula to rearrange the cell to read "John, Smith"?


Thanks

 

[kweaver]

What about:

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

 

[JEC]

What if a name has 3 words. Rembrandt van Rijn

 

[JEC]

Whenever you have a 3 word name, you could use this UDF (behind a module).

Function jec(cell As String) As String
c00 = Trim(Split(cell, ",")(UBound(Split(cell, ","))))
jec = c00 & ", " & Replace(Replace(cell, ",", ""), c00, "")
End Function

=jec(A1)

 

[johnnyL]

This will handle it:

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