Split an employee ID into department and hire year
The department and hire year are baked into the ID — once you know where to cut.
Task:
You work in HR analytics and need a headcount report broken down by department and hire year. Employee IDs follow a fixed pattern: three letters for the department, a dash, the four-digit hire year, a dash, then a sequence number. Split each ID: put the department in column C and the hire year in column D.
Learning Objectives:
- Pull a fixed number of characters from the start of a string with LEFT
- Pull a chunk from partway through a string with MID, once you know where it starts
- Recognize when fixed-width parsing is safe, and when a changing format would break it
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | Name | Department | Hire Year |
| 2 | ENG-2021-0143 | Priya Shah | ||
| 3 | MKT-2023-0087 | Owen Kessler | ||
| 4 | SAL-2019-0052 | Dana Okafor | ||
| 5 | ENG-2024-0201 | Mateo Ruiz |
What this exercise teaches (contains the answer)
LEFT(A2,3) only works because every department code is exactly three letters — a four-letter code like "SALES" would silently come back as "SAL". MID(A2,5,4) leans on the same fixed layout: character 5 is the year's first digit because the three-letter code and its dash always occupy positions 1 through 4, so counting to 5 never has to look at the data to know where the year begins. Both formulas trade the robustness of searching for a delimiter for the simplicity of counting fixed positions — a trade that only stays safe as long as the ID format itself never changes.