Easily change formatting of names in Excel sheets
Surname, Forename or Forename Surname? ...or even First name and Surname in separate columns?
We all have our preferences but unfortunately so do the different software packages we use.
This post shares some Excel formulas to quickly and easily switch around the formatting of names.
The formulas used are summarised in the image below:
 |
| Click to enlarge |
The first formulas search for a comma and return the characters on either the left (for surname) or the right (for first name). Just change the cell reference to point to the cell with Surname, Forename.
For surname: =LEFT(A2,(FIND(",",A2)-1))
For forename: =RIGHT(A2,(LEN(A2)-1-FIND(",",A2)))
The next formula will take a forename and a surname from separate cells and join them to Forename Surname: =B2&" "&C2
If you want to take forenames and surnames from separate cells and output as Surname, Forename use: =C2&", "&B2
And finally to convert Forename Surname to Surname, Forename: =RIGHT(D2,(LEN(D2)-FIND(" ",D2)))&", "&LEFT(D2,(FIND(" ",D2))).
Try this out with this example spreadsheet (be aware this is LIVE and shared publicly!)
