Home  >  Article  >  How to split names in Excel?

How to split names in Excel?

WBOY
WBOYforward
2023-04-20 23:34:078150browse

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:

  1. From the work to separate Select all full names in the table. Make sure not to select any title - just the name.

如何在 Excel 中分隔名称

  1. Click the Data tab on the ribbon and click Text to Column.

如何在 Excel 中分隔名称

  1. In text columns The wizard will start. Check Separate and click Next.

如何在 Excel 中分隔名称

  1. 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.

如何在 Excel 中分隔名称

  1. 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.

如何在 Excel 中分隔名称

  1. 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.

如何在 Excel 中分隔名称

  1. Open the Text Column Wizard again, and the correct target cell will be displayed. Click Done

如何在 Excel 中分隔名称

  1. The first and last name will populate your spreadsheet, now separate. Continue working on your document, or save your work for later.

如何在 Excel 中分隔名称

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:

  1. 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.

如何在 Excel 中分隔名称

  1. In cell D2, manually type the last name of record B2 - in this example For Burgess.

如何在 Excel 中分隔名称

  1. 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.

如何在 Excel 中分隔名称

  1. In the "Data" tab, click "Quick##" in "Data Tools" #Fill”Button

如何在 Excel 中分隔名称

    Excel will instantly separate the first name from the middle and last names and fill them in with the Spreadsheet section.

如何在 Excel 中分隔名称

  1. 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 中分隔名称

  1. Excel automatically populates the D columns with last names and separates them from first and middle names.

如何在 Excel 中分隔名称

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!

Statement:
This article is reproduced at:yundongfang.com. If there is any infringement, please contact admin@php.cn delete