Recently, a friend asked about fixing an Excel spreadsheet where his subscriber names were in 1 column. The problem was the cell contained the full name. He wanted separate first and last names in Excel so he could send a personalized mailing. After all, most of us would prefer getting an email saying “Hello Anne” instead of “Hello Anne Zachary”. Excel has a handy feature called Convert Text to Columns Wizard that can help solve this problem.
Although this problem is about a Name field, the same type of issue can happen elsewhere. It’s easy to create spreadsheets without thinking how the data will be used. I’ve been guilty of this myself.
The key to solving this problem is to parse or split the data. If you go to the Data tab, you’ll see a Text to Columns item. It seems people dismiss this choice because their data is already in columns.
How to Separate First and Last Names in Excel,
- Right-click the column header that is to the right of the names you wish to split and select Insert. For example, if your names are in Column A, click Column B. This new blank column is where we will put the Last Names.
- Click the column header of the column you wish to split. In my case, the names are in Column A.
- From the Data menu, select Text to Columns. A data wizard appears.
- Choose the Delimited radio button in the Original data type section.
- Click Next.
- In the Delimiters section, select the item that separates your data. In this example, a space separates the First and Last name.
- Look at the Data preview section. If the data looks correct, click Next.
- Click the first column in the Data preview section and select the Column data format type. I’ve kept the default Destination range.
- Click Finish.
- Label your new column.
Want More Tips & Tools?
Several times a month, we also publish a free newsletter with relevant stories, tips and special offers.