Split Product Codes with LEFT, RIGHT and LEN
Your task: In B2:D4, extract each code's country and last four characters, and flag codes that are not 7 long.
Full instructions, scenario and goal
Scenario
A logistics team receives product codes from several suppliers. A valid code is a two-letter country, a dash and a four-digit number, such as US-1042 (seven characters). The team wants the country and the number in separate columns, and a warning on any code that is not seven characters long.
Your task
- In B2:B4, extract the two-letter country code from the start of each product code.
- In C2:C4, extract the last four characters of each product code.
- In D2:D4, show Valid when the code is exactly 7 characters long, otherwise show Check.
- Tip: write the three formulas for row 2, then copy them down. Use the code in column A, then select Check Answer.
You are done when
- Country and number are taken from the code in column A, not typed.
- A code that is longer or shorter than 7 characters is flagged as Check.
What you will practise
Take characters from the start or end of a text value with LEFT and RIGHT, and measure it with LEN.
The idea in 30 seconds
Text functions let a formula read part of a cell. LEFT(text, n) returns the first n characters, RIGHT(text, n) returns the last n, and LEN(text) returns how many characters the text has, counting spaces and hyphens. When codes follow a fixed pattern (two letters, a dash, four digits), these three functions are enough to take them apart without retyping anything.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell B2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Text functions let a formula read part of a cell. LEFT(text, n) returns the first n characters, RIGHT(text, n) returns the last n, and LEN(text) returns how many characters the text has, counting spaces and hyphens. When codes follow a fixed pattern (two letters, a dash, four digits), these three functions are enough to take them apart without retyping anything.
The result of LEFT and RIGHT is always text, even when it looks like a number: RIGHT of a code gives "4471", not 4471. That is usually what you want for IDs, because it keeps leading zeros. Combine LEN with IF to check data quality: if a code should be 7 characters long, any other length is worth a second look. Always take the text from the cell instead of typing it, so the formula keeps working when the code changes.
Skills you will practise
- LEFT
- RIGHT
- LEN
- IF
- Text extraction
Topic guide: Excel Text Functions Practice: TRIM, LEFT, TEXT and More
Complete first: Clean and Combine Names with TRIM and PROPER
Common mistakes to avoid
- Typing the extracted text
- A typed "US" does not change when the code does. Use LEFT on the cell.
- Cutting from the wrong end
- LEFT takes from the start and RIGHT from the end. Swapping them returns the wrong part of the code.