Chapter 06 · basic · 5 min
Text I: LEFT, RIGHT, MID, LEN, FIND, SEARCH
Real data arrives as one string that actually holds several fields glued together — an order ID, a SKU, a formatted code. Excel's text functions are how you pull the piece you need back out: LEFT/RIGHT for fixed-position slices, MID for anything in the middle, FIND/SEARCH for locating a delimiter when you don't know its position ahead of time.
We'll pull pieces out of one Lazada SKU, LZD-ELEC-2205, throughout this chapter.
The grid
A single Lazada SKU string in A1: LZD-ELEC-2205 — a seller prefix, a category code, and a product number, joined by hyphens.
LEFT and RIGHT: fixed-position slices
When the piece you want always starts at the same edge and is always the same length, LEFT/RIGHT are the direct tool:
=LEFT(A1,3)returns the first 3 characters — here, the seller prefix.
A1 is "LZD-ELEC-2205". What are its first 3 characters?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | LZD-ELEC-2205 | =LEFT(A1,3) |
RIGHT, and why it's the wrong tool here
=RIGHT(A1,4)returns the last 4 characters — the product number. This works because the product number happens to always be 4 digits. The moment it isn't, RIGHT with a hardcoded length breaks the same way a hardcoded LEFT would — it's a fixed-position tool, not a delimiter-aware one.
What are the last 4 characters of "LZD-ELEC-2205"?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | LZD-ELEC-2205 | =RIGHT(A1,4) |
MID + FIND: the middle segment, located dynamically
The category code sits between the two hyphens, and nothing about its position is fixed the way the prefix and product number are. FIND("-",A1) locates the first hyphen; FIND("-",A1,FIND("-",A1)+1) searches again, starting just past it, to find the second. MID then slices out everything between them:
=MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)The category code
In D1, extract the category code sitting between the two hyphens in A1, without hardcoding their positions.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | LZD-ELEC-2205 |
FIND vs SEARCH
FIND is case-sensitive and takes its search text literally. SEARCH is case-insensitive and accepts wildcards (? for any one character, * for any run of characters). For a fixed delimiter like a hyphen, either works identically — reach for SEARCH when you need to match text regardless of case, or need a wildcard; reach for FIND when the match must be exact.