Product codes like “US-2026-0042” hide three pieces of information in one cell: region, year, and serial number. LEFT, RIGHT, and MID pull those pieces out, so you can sort by region, group by year, or match on the serial alone. They are the simplest text tools in Excel, and they show up in almost every data-cleaning job.
What LEFT / RIGHT / MID does
LEFT takes characters from the start of a text, RIGHT from the end, and MID from a position you choose. All three count characters, not words, and they return text — even when the characters are digits. Pair them with FIND to locate a separator first, and you can split any structured code without typing anything by hand.
The syntax
=LEFT(text, num_chars) — =RIGHT(text, num_chars) — =MID(text, start_num, num_chars)
- text — the cell or text to take characters from.
- num_chars — how many characters to take.
- start_num — for MID only: the position to start at, counting from 1.
Step-by-step example
Split product codes of the form US-2026-0042 into region, year, and serial.
- Click the cell for the region.
- Type
=LEFT(A2,2)— the first two characters are the region. - For the year in the middle, type
=MID(A2,4,4)— start at character 4 and take 4 characters. - For the serial at the end, type
=RIGHT(A2,4)— the last four characters. - If the parts vary in length, find the separator first:
=LEFT(A2,FIND("-",A2)-1)takes everything before the first dash, however long it is.
Worked example
| Code | Region (LEFT) | Year (MID) | Serial (RIGHT) |
|---|---|---|---|
| US-2026-0042 | US | 2026 | 0042 |
| EU-2025-1187 | EU | 2025 | 1187 |
| KR-2026-0093 | KR | 2026 | 0093 |
The same three functions handle every row the same way, because the code structure never changes. When the structure does change — a longer region, a missing dash — that is when FIND-based formulas earn their keep.
Common errors
Off-by-one characters. MID counts from 1, and the dash occupies a position too. In “US-2026-0042” the year starts at position 4, not 3. Count the separators before you type the number.
Numbers come back as text. RIGHT returns “0042” as text, and "0042"=42 is FALSE. To calculate with the result, wrap it in VALUE: =VALUE(RIGHT(A2,4)) — but note VALUE drops the leading zeros.
#VALUE! from FIND. FIND errors when the separator is not there at all. Guard it: =IFERROR(LEFT(A2,FIND("-",A2)-1),A2) returns the whole cell when there is no dash.
Too many or too few characters. LEFT and RIGHT silently return whatever exists if you ask for more characters than the text has. If the result looks short, the source text is shorter than you think — check with LEN.
Tip
For a one-off split, Text to Columns is faster than formulas. Use LEFT, RIGHT, and MID when the data keeps arriving and the split has to update itself — a live formula beats re-running a manual step every week.
Extract a file extension or an email domain
The same three functions handle the two extractions people ask for most. A file extension is the text after the last dot: =RIGHT(A2,LEN(A2)-FIND(".",A2)) works when there is exactly one dot, and for names with several dots use =MID(A2,FIND("~",SUBSTITUTE(A2,".","~",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))+1,99) — ugly, but it finds the last dot. An email domain is simpler: =MID(A2,FIND("@",A2)+1,99) takes everything after the @ sign. These look messy because they are built from the same small pieces — FIND to locate, LEN to measure, MID or RIGHT to cut — and once you see the pattern, you can assemble any extraction the same way.
Practice file
Download the LEFT / RIGHT / MID practice file (.xlsx) — it contains product codes with live LEFT, RIGHT, and MID formulas, plus a FIND-based version for variable-length codes and a blank sheet for you to try it yourself.
Google Sheets
Google Sheets supports LEFT, RIGHT, and MID with the same syntax. Sheets also has SPLIT, which breaks text at a separator into several cells at once — often a one-step alternative to three formulas.
Example prices and figures are illustrative, not market data.