Chapter 06 · basic · 6 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 split a column of five Lazada SKUs throughout this chapter. They share one shape — seller, category, product number, joined by hyphens — but not one length, and that difference is the whole lesson.
The grid
Column A holds five Lazada SKUs, each a seller prefix, a category code and a product number joined by hyphens. The categories run from 4 to 7 letters and the product numbers from 2 to 4 digits. Columns B to E are where the pieces go.
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. Every seller prefix here is exactly 3 characters, so:
=LEFT(A2,3)returns the first 3 characters of A2, and would return the right answer on every row.
A2 is "LZD-ELEC-2205". What are its first 3 characters?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Seller | Last 4 chars | Category | Product no. |
| 2 | LZD-ELEC-2205 | =LEFT(A2,3) | |||
| 3 | LZD-HOME-318 | ||||
| 4 | LZD-BEAUTY-7741 | ||||
| 5 | LZD-TOYS-1090 | ||||
| 6 | LZD-FASHION-52 |
RIGHT, and why it's the wrong tool here
The product number sits at the right-hand end, so RIGHT(A2,4) looks like the obvious move — and on A2 it returns 2205, exactly right. But that only works because 2205 happens to be 4 digits. Row 3's product number is 318:
=RIGHT(A3,4)RIGHT has no idea where the product number starts; it just counts 4 characters back from the end. A hardcoded length is a guess about the data, and the guess is only as good as the shortest row. Row 6, with its 2-digit product number, is worse still.
A3 is "LZD-HOME-318". What are its last 4 characters — and is that the product number?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Seller | Last 4 chars | Category | Product no. |
| 2 | LZD-ELEC-2205 | ||||
| 3 | LZD-HOME-318 | =RIGHT(A3,4) | |||
| 4 | LZD-BEAUTY-7741 | ||||
| 5 | LZD-TOYS-1090 | ||||
| 6 | LZD-FASHION-52 |
MID + FIND: the middle segment, located dynamically
The category code sits between the two hyphens, and it runs from 4 letters (ELEC) to 7 (FASHION), so no fixed length can slice it out. Instead, let the hyphens tell you where to cut:
FIND("-",A2)returns where the first hyphen is.FIND("-",A2,FIND("-",A2)+1)searches again, starting just past the first hyphen, and returns where the second one is.MID(text, start, length)then slices out everything between them: start one past the first hyphen, length is the gap between the hyphens minus one.
=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)It's long, but every piece is one of those three ideas. The breakdown under the grid shows each piece's value on row 2 — hover a step to see which cell it reads. Change A2 to one of the longer SKUs and the formula still cuts in the right place.
The hyphens in "LZD-ELEC-2205" sit at positions 4 and 9. What text sits between them?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Seller | Last 4 chars | Category | Product no. |
| 2 | LZD-ELEC-2205 | =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1) | |||
| 3 | LZD-HOME-318 | ||||
| 4 | LZD-BEAUTY-7741 | ||||
| 5 | LZD-TOYS-1090 | ||||
| 6 | LZD-FASHION-52 |
How Excel works it out · run it to see each value
- 1FIND("-",A2)=?first hyphen
- 2FIND("-",A2,FIND("-",A2)+1)=?second hyphen, searching past the first
- 3FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1=?letters between them
- 4MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)=?the slice between them
Fixing RIGHT: everything after the second hyphen
The same trick repairs the product number. Find the second hyphen, then take everything after it. LEN(A2) returns how many characters A2 has, which is always at least as many as are left after the hyphen — and MID simply stops at the end of the text when you ask for more than is there:
=MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,LEN(A2))RIGHT(A2,LEN(A2)-FIND(...)) works too: total length minus the second hyphen's position is exactly how many characters sit after it. Either way, no number in the formula is a guess about the data. Get row 2 right, then fill it down: row 3 should come back as 318, not -318, and row 6 as 52.
The product number of row 2, located by its hyphen
In E2, return the product number — everything after the second hyphen — without hardcoding its length. Then fill down and check rows 3 and 6.
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Seller | Last 4 chars | Category | Product no. |
| 2 | LZD-ELEC-2205 | ||||
| 3 | LZD-HOME-318 | ||||
| 4 | LZD-BEAUTY-7741 | ||||
| 5 | LZD-TOYS-1090 | ||||
| 6 | LZD-FASHION-52 |
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.