What do you do when you need to clean up your spreadsheet? For example, you might want to split the names into two columns instead of one. Or, how about having first name, middle name, and last name in one column? If this happens to you, Microsoft Excel 2013 and later has excellent built-in tools that allow you to separate them.
If you need to separate names in Excel, follow this tutorial and we'll show you how.
Separate Names in Excel
To separate first and last name into different columns in Excel, do the following:
- From the work to separate Select all full names in the table. Make sure not to select any title - just the name.
- Click the Data tab on the ribbon and click Text to Column.
- In text columns The wizard will start. Check Separate and click Next.
- On the following screen of the wizard, in the Delimiters list, check Spaces and cancel Check any other separators. You are checking for spaces because a single space separates the names in the selected row. Click Next.
- Now, select where in the spreadsheet you want the separated first and last names to appear. Begin by clicking on the Target field and clearing its contents. Then click the up arrow icon to select the cells whose names you want to display.
- For this simple worksheet, we want to display the names in column C and the names in column D Show last name in . Select cell C2 on the spreadsheet and click the down arrow icon.
- Open the Text Column Wizard again, and the correct target cell will be displayed. Click Done
- The first and last name will populate your spreadsheet, now separate. Continue working on your document, or save your work for later.
Separating first and last names with middle names in Excel
Separating first and last names is easy, but what if you have a middle name too? good news. You can use the QuickFill feature in Excel 2013 or later. Unfortunately, the Quick Fill feature is not part of older versions of Excel.
You can separate first and last names from middle names in Excel by doing the following:
- Open the spreadsheet that contains the names you want to sort. Click the cell where you want the name to appear. In the following example, it is C2. Then enter the name manually. In this case, it's Brian.
- In cell D2, manually type the last name of record B2 - in this example For Burgess.
- Now it’s time to activate Flash Fill. Click the C2 cell where you manually entered the Name and select the Data tab on the ribbon.
- In the "Data" tab, click "Quick##" in "Data Tools" #Fill”Button
Excel will instantly separate the first name from the middle and last names and fill them in with the Spreadsheet section. -
- Now that this works well, let’s do the same thing with the last name. Click on the cell where you manually entered the last name - in this case, it is D2. On the ribbon, click the Data tab, and then click the Quick Fill button in the Data Tools section.
- Excel automatically populates the D columns with last names and separates them from first and middle names.
Everything here belongs to it. We've provided a basic spreadsheet, but the instructions are suitable for the most extensive and complex workbooks. If you need to separate names in Excel, follow the tutorial above and you'll be set.
Excel has several cool features, such as custom sorting and conditional formatting. You can also represent your data visually by creating pie charts or using scatter plots.
The above is the detailed content of How to split names in Excel?. For more information, please follow other related articles on the PHP Chinese website!