Sunday 5 April 2009

The first shall be last and the last shall be first ( in Excel )

I had a need in Excel to swap the words round in a field. The was for address book processing where I wanted to change a "Last,First" name into a "First Last" combination. The address book has been exported Both names were in the same column and the output was needed in another column.



The second column is a formula like this from cell B1.

=REPLACE(A1,FIND(",",A1),100,"" ) & " " & REPLACE(A1,1,FIND(",",A1),"")


Three are three parts to this formula each separated by &.
The first part takes the characters from cell A1 from the comma for the next 100 characters "First"
The second part is a Space character " ".
The third part takes the characters from cell A1 from the first character up the comma. "Last"
The result showing in cell B1 is the parts joined up, the action of the & between the parts. "First Last"