Wednesday, October 06, 2004

Reversing firstnames and surnames in a list

Today I wrote this formula for a friend who needed to convert a database field displaying names as surname firstname to firstname surname.
His data had been checked to see that only one space existed between each name in the list.
The formula written here was designed to provide the answer in Column B for the data in Column A.
=CONCATENATE(RIGHT(A1,LEN(A1)-FIND(" ",A1))," ",LEFT(A1,(FIND(" ",A1)-1)))
Once the formula had been filled down I instructed him to Copy it and then Paste Special As Values in the same place.
(More details on the Paste Special function tomorrow)