Excel offers built-in features to separate first, middle and last names. Keep reading this article to separate names in Excel depending on the separator you’ve used to separate the words.
How to Separate Names in Excel?
If you wish to separate names into separate columns, you can use the Text to Column tool to separate each text. You can also use formulas using functions like LEFT/RIGHT, LEN, SEARCH, and SUBSTITUTE to separate first middle and last names. Then, you can use Flash Fill to fill in the rest of the cells. Similarly, you can use the Power Query to separate full names if you’re an advanced user.
Use Text to Columns
The Text to Columns tool in Excel changes the alignment of your cell value and can also be used to add space between your names. When using this tool, your information will be separated into two columns.
Use Library Functions
You can use functions readily available in Excel to extract first and last names, separated by a space or comma. Additionally, you can also extract middle names that are separated by a space.In the given formulas, in place of the “cell”, enter the location of the cell with the full name.
Separated by Space
If a space separates the full names, you can use these steps to extract the first, last and middle names into different columns.First NameMiddle NameLast Name
Separated by Comma
You will have to use a different formula to extract the first and last names if commas separate your full names.First NameLast Name
Use Power Query
If you’re using the 2016 version of Excel or later, you can use Power Query to split the full names into different columns. As using Power Query is a bit complicated, we advise only advanced Excel users to use this method.Before opening your data in Power Query, convert your data to a table. Select your data from the grid, then head to the Insert tab, then select Table. After you’ve converted your data to a table, you can load your table into the Power Query.
Use Third Party Add-ins
You can also use third-party add-ins to separate names in Excel. Using third-party add-ins is more convenient than using the Text to Column tool or the Excel functions. However, you must be aware that third-party add-ins can put your device at risk of malware.
How to Automate Separating Names by Creating a Macro?
If you regularly have to separate first and last names, you can create a Macro to automate this task. In Excel, Macros register your keystrokes to create a Visual Basic code. When you record a Macro, Excel will generate a code following your keystrokes that you can run to replicate the action.After you record your Macro from the developer tab, you can continue to separate the full names from the grid following the above-mentioned methods. After you record your Macro, you can run the Macro the next time you need to separate names..
