Excel function guide · Text
LEFT, RIGHT and MID functions in Excel
LEFT returns a number of characters from the start of a text value, RIGHT from the end, and MID from any position you choose. They're the standard way to split codes like LDN-4821-RED into their parts.
Syntax
=LEFT(text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num, num_chars)
| Argument | Required? | What it means |
|---|---|---|
| text | Required | The text (usually a cell) to take characters from. |
| num_chars | Optional | How many characters. Optional for LEFT and RIGHT (default 1), required for MID. |
| start_num (MID) | Required | Position of the first character to take, counting the first character as 1. |
LEFT, RIGHT and MID examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | |
|---|---|---|
| 1 | SKU | Product |
| 2 | LDN-4821-RED | Desk lamp |
| 3 | MAN-1307-BLK | Office chair |
| 4 | EDI-5590-WHT | Monitor arm |
| 5 | BRS-0042-GRN | Plant pot |
1. From the start
=LEFT(A2, 3)
Result: LDN
The first three characters: the location code.
2. From the middle
=MID(A2, 5, 4)
Result: 4821
Four characters starting at position 5 (after LDN and the hyphen). The result is text, not a number.
3. From the end
=RIGHT(A2, 3)
Result: RED
The last three characters: the colour code.
Try it now
Practise LEFT, RIGHT and MID in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In C2, extract the location: the first 3 characters of the SKU.
Common LEFT, RIGHT and MID mistakes
Digits come back as text
MID(A5, 5, 4) returns the text "0042". Wrap it in VALUE to calculate with it: VALUE(MID(A5, 5, 4)) is the number 42.
Counting from 0
Positions start at 1. In LDN-4821-RED the hyphen is character 4, so the number starts at 5.
Codes of different lengths
Fixed positions only work when every code has the same pattern. For variable lengths, find the delimiter first: LEFT(A2, FIND("-", A2)-1).
Hidden spaces
A leading space shifts every position by one. Clean the column with TRIM before extracting.
Practise this in Excel Quest
Extract codes with LEFT, MID, RIGHT & LEN Members
Course 7 · Text & Data Cleaning · 11 min
Split structured SKUs into category, item, number and market — and turn the number back into a real number.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.