Split text to columns in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to organize customer contact information by separating full names into first and last names in the spreadsheet.

📊 Data: You have a list of customers with their full names in one column, and you need to split these names into two separate columns: First Name and Last Name.

🎯 Deliverable: A cleaned spreadsheet where the full names are split into First Name and Last Name columns for easier sorting and filtering.

Sample Data

Customer ID	Full Name	Email
101	John Smith	[e-mail]
102	Mary Johnson	[e-mail]
103	James Brown	[e-mail]
104	Patricia Taylor	[e-mail]
105	Michael Davis	[e-mail]
106	Linda Wilson	[e-mail]
107	Robert Moore	[e-mail]
108	Barbara Clark	[e-mail]

1

Step 1: Insert two new columns to the right of the 'Full Name' column and label them 'First Name' and 'Last Name'.

No formula needed for this step.

Expected Result

Two empty columns named 'First Name' and 'Last Name' appear next to 'Full Name'.

2

Step 2: In the first cell under 'First Name' (e.g., C2), enter a formula to extract the first name from the full name.

=INDEX(SPLIT(B2, " "), 1)

Expected Result

The first name 'John' appears in cell C2.

3

Step 3: In the first cell under 'Last Name' (e.g., D2), enter a formula to extract the last name from the full name.

=INDEX(SPLIT(B2, " "), 2)

Expected Result

The last name 'Smith' appears in cell D2.

4

Step 4: Copy the formulas in C2 and D2 down the columns to fill all rows with first and last names.

Drag the fill handle from C2 and D2 down to row 9.

Expected Result

All full names are split into first and last names in their respective columns.

5

Step 5: Alternatively, use the built-in 'Split text to columns' feature to separate the full names.

Select the 'Full Name' column, then go to Data > Split text to columns, and choose Space as the separator.

Expected Result

The 'Full Name' column splits into two columns automatically with first and last names.

Final Result

Customer ID | First Name | Last Name | Email
----------------------------------------------------------
101         | John       | Smith     | [e-mail]
102         | Mary       | Johnson   | [e-mail]
103         | James      | Brown     | [e-mail]
104         | Patricia   | Taylor    | [e-mail]
105         | Michael    | Davis     | [e-mail]
106         | Linda      | Wilson    | [e-mail]
107         | Robert     | Moore     | [e-mail]
108         | Barbara    | Clark     | [e-mail]

✓Splitting full names into first and last names makes sorting and filtering easier.

✓Using the SPLIT formula or the built-in feature both achieve the goal efficiently.

✓Organized data improves clarity and helps with customer communication.

Bonus Challenge

Modify the formula to handle middle names or multiple spaces in full names, extracting only the first and last names.

Show Hint

Use the INDEX and SPLIT functions together, for example: =INDEX(SPLIT(B2, " "), 1) for first name and =INDEX(SPLIT(B2, " "), COUNTA(SPLIT(B2, " "))) for last name.