Splitting one column into several with Text to Columns

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which spreadsheet tool splits a full name in one column into first and last name columns?

A Merge Cells

B Text to Columns

2. The cell A2 holds 'Ava Nguyen'. Which delimiter should you choose to split it into first and last name?

A Space

B Comma

3. The cell A2 holds 'Nguyen,Ava'. Which delimiter should you choose to split it?

A Comma

B Space

4. What is a delimiter in Text to Columns?

A The character that marks where to split the text, such as a comma or space

B The column the split data is moved into

5. After splitting 'Liam Smith' at a space, what ends up in the columns?

A Liam Smith in both columns

B Liam in one column and Smith in the next column

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Data > Text to Columns		ADelimiter: Comma
2Delimiter: Space		BSelect A2:A9
3Choose a comma as the delimiter to split a cell like Smith,Liam into last name and first name		CThe delimiter does not match the data. The names are separated by a space, so a comma finds nothing to split and the name stays in one column.
4Before splitting, select the column that holds the full names so Text to Columns knows which text to split		DText to Columns writes into the columns to the right and can overwrite the data already in B and C.
5Data has "Ava Nguyen" but Delimiter: Comma is chosen		ESplits "Ava Nguyen" into "Ava" in one column and "Nguyen" in the next
6Split A2:A9 with columns B and C already holding data		FOpens the spreadsheet tool that splits the text in one column into several columns

Definitions

Text to Columns

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Data > _______________

Full name in A2: Ava Nguyen Delimiter: _____

Name in A2: Nguyen,Ava Delimiter: _____

Step 1: choose _________ as the data type

Split A2 into B2 and C2 at the _________

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Select D2:D9 (full names)

B Data > Text to Columns

C Choose Delimited

D Delimiter: Space

Question 2

A Select A2:A9 (Nguyen,Ava)

B Data > Text to Columns

C Choose Delimited

D Delimiter: Space

Question 3

A Select A2:A9 (Ava Nguyen)

B Data > Remove Duplicates

C Choose Delimited

D Delimiter: Space

Question 4

A Select C2:C9 (2026-02-03)

B Data > Text to Columns

C Choose Fixed width

D Delimiter: Other, type -

Question 5

A A2:A9 holds full names

B Set Destination to A2

C First name goes in B

D Last name goes in C

Answer Key

Splitting one column into several with Text to Columns

Section A: Quick Questions

B — Text to Columns

A — Space

A — Comma

A — The character that marks where to split the text, such as a comma or space

B — Liam in one column and Smith in the next column

Section B: Match the Pairs

Data > Text to Columns → Opens the spreadsheet tool that splits the text in one column into several columns

Delimiter: Space → Splits "Ava Nguyen" into "Ava" in one column and "Nguyen" in the next

Choose a comma as the delimiter to split a cell like Smith,Liam into last name and first name → Delimiter: Comma

Before splitting, select the column that holds the full names so Text to Columns knows which text to split → Select A2:A9

Data has "Ava Nguyen" but Delimiter: Comma is chosen → The delimiter does not match the data. The names are separated by a space, so a comma finds nothing to split and the name stays in one column.

Split A2:A9 with columns B and C already holding data → Text to Columns writes into the columns to the right and can overwrite the data already in B and C.

Section C: Definitions

Text to Columns — A spreadsheet tool that splits the text in one column into separate columns at a chosen delimiter, such as a comma or space, e.g. a full name into first and last name.

Section D: Fill in the Blanks

Data > Text to Columns

Full name in A2: Ava Nguyen
Delimiter: Space

Name in A2: Nguyen,Ava
Delimiter: Comma

Step 1: choose Delimited as the data type

Split A2 into B2 and C2 at the delimiter

Section E: Spot the Error

A — The full names are in column A, so you must select A2:A9. Text to Columns only splits the cells you select, and D2:D9 does not hold the names.

D — The names are separated by commas, so the delimiter must be Comma. A space delimiter would find nothing to split on.

B — Splitting a name into first and last uses Data > Text to Columns. Remove Duplicates deletes repeated rows and does not split text.

C — The dates are split at a character (the hyphen), so you choose Delimited. Fixed width splits at set positions, which suits data where every piece has the same length.

B — A destination of A2 would overwrite the original full names. Set the destination to B2 so the split pieces go into empty columns and the originals are kept.