Excel Challenge 3: Split First and Last Names

Challenge Scenario

In many offices and businesses, employee or customer names are often typed together into a single column, such as "John Doe" in Column A. However, when you want to send personalized emails (like "Hi John!"), print badge name tags, or sort a list by last name, having everything in one cell causes problems.

Because every name has a different length (for example, "Jane" has 4 letters while "Robert" has 6 letters), we cannot just pick a fixed number of characters. We need smart Excel formulas that look for the space character between the names and split them automatically.

As an HR & Office Data Assistant, your goal is to build formulas for this employee roster:

First Name (Column B): Extract only the first name (all letters before the space) using LEFT and SEARCH.
Last Name (Column C): Extract only the last name (all letters after the space) using RIGHT, LEN, and SEARCH.
Initials Badge (Column D): Combine the first letter of each name into a clean format like "J.D." using LEFT and &.
Total Roster Count (Cell B8): Count total team members using COUNTA.

Your Sheet Layout

Before writing formulas, take a quick look at how this sheet is organized. A good spreadsheet separates raw input data from calculated results so your work is clean and easy to check.

This worksheet is divided into three main sections:

Source Data (Column A): The original full names list. Keep this column untouched as your source.
Split Results & Badges (Columns B, C, D): Your working columns where formulas extract the first name, last name, and make initials.
Summary Section (Row 8): The bottom row where you calculate the total number of people on the list.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:A5`	Full Name	Source Inputs	Original Text	The starting full names. Your formulas will read from this column.
`B2:B5`	First Name	Text Extraction	`=LEFT(A2, SEARCH(" ", A2) - 1)`	Extract all letters that appear before the space character.
`C2:C5`	Last Name	Text Extraction	`=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))`	Extract all letters that appear after the space character.
`D2:D5`	Initials	String Joining	`=LEFT(B2,1) & "." & LEFT(C2,1) & "."`	Take the first letter of First Name and Last Name and join them with dots.
`B8`	Summary: Total Roster	Count Metric	`=COUNTA(A2:A5)`	Count the total number of employee names on the list.

Each row calculates one employee independently from left to right, and row 8 gives you the total count at the bottom.

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: B2-B5

In Column B (B2:B5), extract First Name using LEFT and SEARCH.

Objective #2

Cell: C2-C5

In Column C (C2:C5), extract Last Name using RIGHT, LEN, and SEARCH.

Objective #3

Cell: D2-D5

In Column D (D2:D5), create Initials in "F.L." format using LEFT and & concatenation.

Objective #4

Cell: B8

In cell B8, count total roster members using COUNTA.

Solution & Formula Breakdown

Splitting names in Excel is easy once you understand how Excel finds the space character between words. Here is the step-by-step guide for every formula:

1. Finding the Space Position with SEARCH

The secret to splitting names is finding where the first word ends. In Excel, words are separated by a space (" ").

The SEARCH(" ", text) function tells you the exact character number where the space is located:

In "John Doe", the space is character number 5 (J-o-h-n- ).
In "Robert Johnson", the space is character number 7.

2. Extracting the First Name (Column B)

To get the first name, we tell Excel: "Take letters from the left, up until right before the space."

=LEFT(A2, SEARCH(" ", A2) - 1)

Why do we subtract 1 (- 1)?

Because the space in "John Doe" is at position 5. If we took 5 characters, we would get "John " with an annoying extra space at the end. Subtracting 1 gives us exactly 4 characters: "John".

3. Extracting the Last Name (Column C)

To get the last name, we tell Excel: "Take letters from the right side of the text." But how many letters?

We use simple math: Total Letters minus Space Position = Remaining Letters on the Right.

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

Let's trace "John Doe":

Total length: LEN("John Doe") = 8 characters.
Space position: SEARCH(" ", "John Doe") = 5.
Math: 8 - 5 = 3 characters.
Result: RIGHT("John Doe", 3) gives us "Doe"!

4. Creating Initials (Column D)

To make initials like "J.D.", take 1 letter from the First Name, add a dot, take 1 letter from the Last Name, and add another dot:

=LEFT(B2, 1) & "." & LEFT(C2, 1) & "."

The ampersand (&) glues text pieces together.

5. Counting Total People (Cell B8)

=COUNTA(A2:A5)

We use COUNTA because it counts cells with text names (regular COUNT only counts numbers).

In newer versions of Excel (Excel 365), you can also split text instantly using the modern formula: =TEXTSPLIT(A2, " ") or by pressing Ctrl + E (Flash Fill)!