FIRST NAME and LAST NAME

[drluke]

I need to change a list of names in my spreadsheet: John Smith to Smith J

Is there a single formula I can use to do this?

I know to change the first name to an initial I can use

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

and to reverse the order I can use

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

But how to combine these 2 formulas? Or use another one that's better?

 

[mse330]

Try the below … But if more names or spaces in A1 the formula may not work properly

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

 

[drluke]

Thank you!
The formula does not work for all names - e.g for the name David Jefferies, the result is 'feries J'

 

[drluke]

Sorry - ignore my previous message .....

 

[mse330]

Ok … I hope it is working ok now

 

[drluke]

Yes thank you!

 

[Peter_SSs]

.. or with one less function
=REPLACE(A1,1,FIND(" ",A1),"")&" "&LEFT(A1,1)