Experiences on the Front Lines of User Interfaces and Web Development

Normalize First, Last Names in Excel

Have you ever had a long list of data (perhaps from a contact export, or an email mailing list, etc) where there were some names in the format "Last, First" and others in "First Last"? And, wanting to remove duplicates and be able to accurately sort them, you found yourself manually going through each row? Well, I was in that predicament tonight, while trying to cleanup my Google contacts.

So, I wrote a small excel statement that can be applied to each row and convert "First Last" to "Last, First" and leave any existing "Last, First" rows alone. So, you then have all your data in the same format. (It would be trivial to alter the statement such that you are left with all "First Last" formats too). Perhaps you will find it useful:

=IF( ISERROR( FIND( "," , A2 ) ) = TRUE, RIGHT( A2, LEN( A2 ) - FIND( " ", A2 ) ) & ", " & LEFT( A2, FIND( " ", A2 ) - 1 ), A2 )
comments powered by Disqus