Split Full Name Into Two Columns with First Name and Last Name
Introduction I have often come across lists of names and addresses in Microsoft Excel where the first name and the last name is entered in one field as "FULL NAME." I know all the database administrators and CRM admins will be groaning at the sight of another database that has first and last names in the same column. Recently, I was given a database with over 35,000 names where the first and last name were in a single column. Fortunately with MS-Excel, you can very quickly fix this problem.
Summary
We will be using Microsoft Excel's powerful Text to Columns function to solve this problem.
Detailed Solution
1. The sample worksheet shown below has a column called Full Name . This column contains the full name of a person including the prefix. E.g. Mr. Bill Gates . We want to split this up into three columns: Salutation (prefix)= Mr. ; First Name=Bill ; and Last Name=Gates.
2. Highlight the cells in Column A.
3. Go to Main Menu >> Data >> Text to Columns. You will see the following dialog. Select Delimited and press Next .
4. In this step select Space as the delimiter and click Finish .
5. Clik OK when prompted if you want to overwrite the contents of the destination cells. If this were a real spreadsheet, you should insert a few columns after the full name column to make sure that you don't overwrite the neighbouring cell contents.
6. Once the names have been split into three columns you can rename the column headings to match their data.
