SPLIT function 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 separate customer full names into first and last names for better data analysis.

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

🎯 Deliverable: Create a spreadsheet where the full names are split into two columns: First Name and Last Name.

Sample Data

Customer Full Name
John Smith
Mary Johnson
David Lee
Linda Brown
James Wilson
Patricia Taylor
Michael Davis
Barbara Miller

1

Step 1: Select the cell next to the first full name to prepare for splitting.

Click on cell B2 next to 'John Smith' in A2.

Expected Result

Cell B2 is selected and ready for formula input.

2

Step 2: Enter the SPLIT formula to separate the full name into first and last names.

=SPLIT(A2, " ")

Expected Result

Cell B2 shows 'John' and cell C2 shows 'Smith'.

3

Step 3: Copy the formula down the column to split all full names in the list.

Drag the fill handle from B2 and C2 down to B9 and C9.

Expected Result

All full names from A2 to A9 are split into first names in column B and last names in column C.

4

Step 4: Label the new columns for clarity.

Type 'First Name' in B1 and 'Last Name' in C1.

Expected Result

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

Final Result

Customer Full Name | First Name | Last Name
-------------------|------------|----------
John Smith         | John       | Smith
Mary Johnson       | Mary       | Johnson
David Lee          | David      | Lee
Linda Brown        | Linda      | Brown
James Wilson       | James      | Wilson
Patricia Taylor    | Patricia   | Taylor
Michael Davis      | Michael    | Davis
Barbara Miller     | Barbara    | Miller

✓The SPLIT function easily separates full names into first and last names using space as the separator.

✓This makes it easier to analyze or sort customers by first or last name.

✓The formula can be quickly copied down to handle many rows.

Bonus Challenge

Some customers have middle names. Modify the formula to extract only the first and last names, ignoring any middle names.

Show Hint

Use the INDEX function combined with SPLIT to pick the first and last parts only.