← ACARA Digital Technologies Years 9–10

Splitting one column into several with 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.

Part of: ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 104

Understanding Splitting one column into several with Text to Columns in ACARA Digital Technologies Years 9–10

What it does: The delimiter must match the character that separates the parts in the data, and here that character is a comma. Text to Columns works on the selected column, so you select the cells with the full names first. The first and last name are separated by a space, so Space is the delimiter. There is no comma in 'Ava Nguyen'. A delimiter is the separator character the tool looks for. Each time it finds one, it starts a new column. The split pieces fill the columns to the right and can overwrite what is there. Insert empty columns first, or keep a copy of the raw data.

Common mistakes: 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. The names are separated by commas, so the delimiter must be Comma. A space delimiter would find nothing to split on.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Splitting one column into several with Text to Columns to a class →

This module's key terms are also in a printable crossword (Modules 333–377) and word search (Modules 333–412).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which spreadsheet tool splits a full name in one column into first and last name columns?
Text to ColumnsMerge Cells
Matches
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.
Applying
Meanings
Term
Text to Columns
Meaning
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.
Practice
Select the cells in column A
Testing
Gaps
Data >
Check Answer
Spots
1Select D2:D9 (full names)
2Data > Text to Columns

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Bear' badge — Forest Guardian, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which spreadsheet tool splits a full name in one column into first and last name columns?
A. Merge Cells
B. Text to Columns ✓
Text to Columns splits the text in one column into separate columns. Merge Cells does the opposite job by joining cells into one.
2. The cell A2 holds 'Ava Nguyen'. Which delimiter should you choose to split it into first and last name?
A. Space ✓
B. Comma
The first and last name are separated by a space, so Space is the delimiter. There is no comma in 'Ava Nguyen'.
3. The cell A2 holds 'Nguyen,Ava'. Which delimiter should you choose to split it?
A. Comma ✓
B. Space
The delimiter must match the character that separates the parts. Here the parts are separated by a comma.
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
A delimiter is the separator character the tool looks for. Each time it finds one, it starts a new column.
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 ✓
The text is cut at the delimiter. Each piece goes into its own column, and the space itself is removed.

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.

Key Term

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.

Fill in the Blanks

1. Data > Text to Columns
Hint: Type the name of the Data menu tool that splits one column into several.
2. Full name in A2: Ava Nguyen Delimiter: Space
Hint: Type the name of the character that separates the first and last name.
3. Name in A2: Nguyen,Ava Delimiter: Comma
Hint: Type the name of the punctuation mark that separates the two names.
4. Step 1: choose Delimited as the data type
Hint: Type the option used when a chosen character separates the parts of the text.
5. Split A2 into B2 and C2 at the delimiter
Hint: Type the word for the character that marks where the text is split.

Spot the Error

Question 1
A Select D2:D9 (full names)
B Data > Text to Columns
C Choose Delimited
D Delimiter: Space
Line 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.
Question 2
A Select A2:A9 (Nguyen,Ava)
B Data > Text to Columns
C Choose Delimited
D Delimiter: Space
Line D — The names are separated by commas, so the delimiter must be Comma. A space delimiter would find nothing to split on.
Question 3
A Select A2:A9 (Ava Nguyen)
B Data > Remove Duplicates
C Choose Delimited
D Delimiter: Space
Line B — Splitting a name into first and last uses Data > Text to Columns. Remove Duplicates deletes repeated rows and does not split text.
Question 4
A Select C2:C9 (2026-02-03)
B Data > Text to Columns
C Choose Fixed width
D Delimiter: Other, type -
Line 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.
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
Line 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.

Student with a class code? Join your class →