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.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 teachesMay contain 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.