Excel 2007 : Reversing first+last name & removing space

[anna85]

Hi, I have this big excel project for school. I need to find an expression that reverses the first and last name as well as removing the space in names like Mac Leod. First instance,
in cell A1 i have Peter Mac Leod, and i want Macleod Peter in cell B1. Can anybody help me out?

I did find a formula to switch first and last name =TRIM(REPLACE(A1&" "&LEFT(A1,FIND(" ",A1)),1,FIND(" ",A1)," ")))
but still trying to figure out how to remove the space between ''Mac'' and ''Leod''?

[teylyn]

Try

=SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99)," ","")&" "&LEFT(A1,FIND(" ",A1&" "))

But be aware that the formula does not distinguish between a name part that belongs to the last name and a name part that belongs to the first name. So, if you have two first names, you'll end up with funny things.

Peter Mac Leod MacLeod Peter
Sally Ann Miller AnnMiller Sally

Name parsing is complex, and very often, a single example name is not representative of the whole scope.

[anna85]

Yeah that was a tough one! It works out great, so thank you very much!