You may want to standardize names in Excel if you’re combining multiple sources where the names come in different formats.
Sometimes the first and last name are in different columns. Sometimes you have the full name – or the first and last name separated by a space. And sometimes you have the last name, comma, first name.
Here is how you standardize these names in Excel so they’re all in the same format.
To convert from separate columns to [First Last] or [Last, First] formats, you can simply use the & function.
To convert from [Last, First] to separate columns, use the Text-To-Columns feature, then the TRIM function to remove the leading space from the first name.
The hardest case is converting from [First Last]. Use Text-To-Columns as with [Last, First]. But this creates exceptions for anyone who has two-word first or last names. These have to be corrected manually, because there’s no way for Excel to automatically tell whether someone has a two-word first name or two-word last name.
For example, in the name Mary Kate Smith, versus the name James Van Horn, Excel can’t automatically determine if the middle word in each of these names is part of the first name or last name.
Bonus Video: First Initial Last Name
I received a question on how to convert to [First Initial. Last Name] format. Since that’s a less common need, I split that into a separate video.
To do this, use the LEFT function to extract the first initial, then the & function to combine it with the last name.
You can’t convert from [First Initial Last Name] format to any of the others, since you don’t have the full first name. But you can use the MID function to convert from this format to a list of last names.
And that’s how you standardize names in Excel.