Text to columns in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales data analyst at a retail company.

📋 Request: Your manager wants you to separate the full customer names into first and last names for better analysis.

📊 Data: You have a list of customer full names in one column, with first and last names separated by a space.

🎯 Deliverable: Create two new columns: one for First Name and one for Last Name, by splitting the full names.

Sample Data

Customer ID	Full Name	Purchase Amount
101	John Smith	250
102	Mary Johnson	300
103	James Williams	150
104	Patricia Brown	400
105	Michael Jones	350
106	Linda Garcia	200
107	Robert Miller	275
108	Barbara Davis	325

1

Step 1: Select the column that contains the full names (Column B).

Click on the header of Column B to highlight all full names.

Expected Result

All full names in Column B are selected.

2

Step 2: Open the Text to Columns wizard.

Go to the Data tab on the ribbon and click 'Text to Columns'.

Expected Result

The Convert Text to Columns Wizard opens.

3

Step 3: Choose the data type for splitting.

Select 'Delimited' and click Next.

Expected Result

You move to the delimiter selection screen.

4

Step 4: Select the delimiter that separates first and last names.

Check the box for 'Space' as the delimiter and uncheck others. Click Next.

Expected Result

Preview shows the full names split into two columns at the space.

5

Step 5: Choose the destination for the split data.

Set the destination to Column C (cell C2) to avoid overwriting original data. Click Finish.

Expected Result

First names appear in Column C and last names appear in Column D.

6

Step 6: Rename the new columns.

In cell C1, type 'First Name'. In cell D1, type 'Last Name'.

Expected Result

Columns C and D have headers 'First Name' and 'Last Name' respectively.

Final Result

Customer ID | Full Name       | Purchase Amount | First Name | Last Name
------------|-----------------|-----------------|------------|----------
101         | John Smith      | 250             | John       | Smith
102         | Mary Johnson    | 300             | Mary       | Johnson
103         | James Williams  | 150             | James      | Williams
104         | Patricia Brown  | 400             | Patricia   | Brown
105         | Michael Jones   | 350             | Michael    | Jones
106         | Linda Garcia    | 200             | Linda      | Garcia
107         | Robert Miller   | 275             | Robert     | Miller
108         | Barbara Davis   | 325             | Barbara    | Davis

Bonus Challenge

Split full names that include middle names into three columns: First Name, Middle Name, and Last Name.

Show Hint

Use Text to Columns with space delimiter and check if some rows have three parts. You may need to adjust manually or use formulas to handle middle names.