Extract first and last names

What you will build

Split a two-part contact name for mail merge preparation. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Sample names in the result workbook before filling the formula.

Open the source practice workbook in Google Sheets ↗

Work through the example

Step 1: Confirm A1:A4 contains Full name, An Nguyen, Binh Tran, and Chi Le; each sample name has exactly two space-separated parts.

Step 2: Enter First in B1 and Last in C1. Select B2, enter =SPLIT(TRIM(A2)," "), then fill or copy the formula down through B4. Each formula spills the two name parts into column C on the same row.

Step 3: Temporarily change A2 to An Maria Nguyen. Confirm the formula returns An, Maria, and Nguyen across B2:D2, then restore A2 to An Nguyen so the workbook returns to the checkpoint.

Formula to try

In the practice workbook, enter the formula in B2 and copy it down through B4. Keep C2:C4 and any required spill cells empty so SPLIT has room to return its results.

=SPLIT(TRIM(A2)," ")

Result workbook with First/Last and the verified SPLIT(TRIM(...)) formula.

How this formula works=SPLIT(TRIM(A2)," ")

Overall meaning of the whole formula
- Remove leading and trailing spaces from A2, then split the text into separate parts at each space.
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- `text` is required; it accepts the text value to split. In this lesson the argument passed is `TRIM(A2)`.
- `delimiter` is required; it accepts the character or text used as the separator. In this lesson the argument passed is `" "`.
TRIM(text)
- `text` is required; it accepts the text whose extra spaces are removed. In this lesson the argument passed is `A2`.
Whole-expression evaluation
- Sheets evaluates the innermost function calls first, then passes their results outward through the enclosing expression.
- TRIM(A2) removes leading, trailing and repeated spaces before the name is split.
- SPLIT(text, delimiter) separates text wherever the delimiter occurs.
- " " is a single-space delimiter.
- The returned name parts spill horizontally into adjacent cells, so those cells must be empty.
- The result can occupy multiple cells; the spill area must be clear, and any companion condition ranges must have dimensions compatible with the data being processed.
Why the displayed final result appears
- The returned name parts spill horizontally into adjacent cells, so those cells must be empty.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Full name	First	Last
An Nguyen	An	Nguyen
Binh Tran	Binh	Tran
Chi Le	Chi	Le

SPLIT(TRIM(A2)," ") returns the two parts horizontally; the adjacent destination cells must be empty.

Common mistake and correction

If you see #REF! or the result cannot spill, clear the destination cells to the right of the formula. For names with three or more words, do not force the result into exactly two columns; choose a parsing rule that matches the real data.

Independent practice

Try another three-word name in your workbook copy, note how many parts SPLIT returns, then restore the original sample data.

Key takeaways

✓TRIM removes extra spaces before SPLIT separates the name.
✓SPLIT on a space returns the name parts horizontally and needs empty spill cells.
✓Copy the formula from B2 through B4 to process all three sample rows.
✓Multi-word names can return more than two parts, so this example is not a universal name parser.

Continue learning

Previous lesson: Remove blank rows; Next lesson: Calculate running totals.

What you will build

Split a two-part contact name for mail merge preparation. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the source practice workbook in Google Sheets ↗

Work through the example

Step 1: Confirm A1:A4 contains Full name, An Nguyen, Binh Tran, and Chi Le; each sample name has exactly two space-separated parts.

Step 2: Enter First in B1 and Last in C1. Select B2, enter =SPLIT(TRIM(A2)," "), then fill or copy the formula down through B4. Each formula spills the two name parts into column C on the same row.

Step 3: Temporarily change A2 to An Maria Nguyen. Confirm the formula returns An, Maria, and Nguyen across B2:D2, then restore A2 to An Nguyen so the workbook returns to the checkpoint.

Formula to try

In the practice workbook, enter the formula in B2 and copy it down through B4. Keep C2:C4 and any required spill cells empty so SPLIT has room to return its results.

=SPLIT(TRIM(A2)," ")

How this formula works=SPLIT(TRIM(A2)," ")

Overall meaning of the whole formula
- Remove leading and trailing spaces from A2, then split the text into separate parts at each space.
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- `text` is required; it accepts the text value to split. In this lesson the argument passed is `TRIM(A2)`.
- `delimiter` is required; it accepts the character or text used as the separator. In this lesson the argument passed is `" "`.
TRIM(text)
- `text` is required; it accepts the text whose extra spaces are removed. In this lesson the argument passed is `A2`.
Whole-expression evaluation
- Sheets evaluates the innermost function calls first, then passes their results outward through the enclosing expression.
- TRIM(A2) removes leading, trailing and repeated spaces before the name is split.
- SPLIT(text, delimiter) separates text wherever the delimiter occurs.
- " " is a single-space delimiter.
- The returned name parts spill horizontally into adjacent cells, so those cells must be empty.
- The result can occupy multiple cells; the spill area must be clear, and any companion condition ranges must have dimensions compatible with the data being processed.
Why the displayed final result appears
- The returned name parts spill horizontally into adjacent cells, so those cells must be empty.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Full name	First	Last
An Nguyen	An	Nguyen
Binh Tran	Binh	Tran
Chi Le	Chi	Le

SPLIT(TRIM(A2)," ") returns the two parts horizontally; the adjacent destination cells must be empty.

Common mistake and correction

Independent practice

Try another three-word name in your workbook copy, note how many parts SPLIT returns, then restore the original sample data.

Key takeaways

✓TRIM removes extra spaces before SPLIT separates the name.
✓SPLIT on a space returns the name parts horizontally and needs empty spill cells.
✓Copy the formula from B2 through B4 to process all three sample rows.
✓Multi-word names can return more than two parts, so this example is not a universal name parser.

Continue learning

Previous lesson: Remove blank rows; Next lesson: Calculate running totals.

Extract first and last names

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Extract first and last names

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments