We’re going to go over how to separate names in Microsoft Excel (e.g., first name, middle name, and last name).
Link to Download the Example Excel File
Click on the Excel icon below to download the example Excel spreadsheet used in the video above.
Separate Names in Excel Using the “Text to Columns” Tool
1. Highlight all of the names (i.e., the names that are currently combined that you would like to separate).
2. On the Data tab on the Excel ribbon, Click on “Text to Columns.”
3. In the dialog box that pops up, make sure “Delimited” is selected, and then click “Next.”
4. Notice what is in between the names you are trying to separate (e.g., commas and spaces), and make sure there is a check in the box next to each of these “delimiters.” For example, if the names are separated by commas and spaces, make sure there is a check in the box next to both “comma” and “space.” Also, make sure there is no check in any box that is not relevant (e.g., if there are no semicolons separating the names, make sure there is not a check in the box next to “Semicolon”).
If something is separating the names that is not listed in the “Delimiters” section, you can simply type it into the box next to where it says “Other.” You can leave the box checked next to “Treat consecutive delimiters as one.”
Finally, check the “Data Preview” section at the bottom of the dialog box to make sure the names are showing up in separate columns.
When you have completed these steps, click on “Next.”
5. On this screen we’re going to show Excel where we would like Excel to place the separated names. Delete what you see in the box next to where it says “Destination” (e.g., $B$6 below) and then click on the icon to the right of this box.
Simply click on the cell where you would like Excel to paste the separated names. For example, below you can see that the box now shows a value of “$D$6,” which showed up in the box automatically by simply clicking on cell D6. If you happen to see something unusual in this box (e.g., “$B$6+$D$6,” simply delete what is in the box and then click on the cell where you would like Excel to paste the separated names. When you are finished, click on the icon on the far right of the dialog box.
Finally, click on the “Finish” button at the bottom right of the dialog box.
Now the separated names are listed in separate columns at the location you specified above.
I hope you found this to a helpful overview of how to separate names in Microsoft Excel. Please leave a comment below and like us on Facebook so your friends can benefit from these resources as well.