🧭 Introduction: The Hidden Structure in Your Data
Ever looked at something like this?
INV-2025-001
SKU-UK-999
EMP-007-JS
Those strings look random — but they’re actually structured data.
Each part has meaning: type, year, region, ID.
With Excel’s LEFT(), MID(), and RIGHT() functions, you can slice out any piece automatically — no text-to-columns, no Power Query, no VBA.
💡 The Three Text Extraction Functions
| Function | What It Does | Example |
|---|---|---|
LEFT(text, num_chars) | Extracts text from the start | =LEFT(A2,3) → INV |
MID(text, start_num, num_chars) | Extracts text from anywhere | =MID(A2,5,4) → 2025 |
RIGHT(text, num_chars) | Extracts text from the end | =RIGHT(A2,3) → 001 |
They work like scissors — cutting your text exactly where you tell Excel to.
🔹 Example 1: Extracting Parts of an Invoice Code
Let’s start with this example:
| A (Raw Code) | Formula | Result |
|---|---|---|
| INV-2025-001 | =LEFT(A2,3) | INV |
| INV-2025-001 | =MID(A2,5,4) | 2025 |
| INV-2025-001 | =RIGHT(A2,3) | 001 |
Breakdown:
- LEFT(A2,3) → first 3 characters (
INV). - MID(A2,5,4) → start at character 5, extract 4 characters (
2025). - RIGHT(A2,3) → last 3 characters (
001).
✅ Result: Split cleanly into code, year, and number.
🔹 Step-by-Step Logic of MID()
=MID(A2,5,4)
| Argument | What It Means | In This Case |
|---|---|---|
text | Cell to look in | A2 |
start_num | Character to start from | 5 (skips “INV-”) |
num_chars | Number of characters to extract | 4 (the digits “2025”) |
You could even make this dynamic with FIND():
=MID(A2,FIND("-",A2)+1,4)
Now Excel finds the dash instead of assuming “5” — works for varying code lengths.
🔹 Example 2: Extract Region Codes Dynamically
| A (SKU) | Formula | Result |
|---|---|---|
| SKU-UK-999 | =MID(A2,FIND("-",A2)+1,2) | UK |
Explanation:
- FIND(“-“,A2) → position of first dash (4)
- Add 1 → start at character 5
- Extract 2 characters → “UK”
✅ Output: UK
🔹 Example 3: Extract Last Digits from Employee Codes
| Code | Formula | Result |
|---|---|---|
| EMP-007-JS | =RIGHT(A2,2) | JS |
Simple and powerful — RIGHT cuts from the end, no matter how long the text.
⚡ Dynamic Extraction Using FIND + LEN
When your codes vary in length, hardcoding (like =MID(A2,5,4)) can break.
Use FIND() and LEN() to make it flexible:
=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
This monster formula finds both dashes and extracts whatever is between them — whether it’s 2 letters or 5 digits.
✅ Perfect for mixed data like INV-2025-001 or SKU-USA-98765.
🧠 Pro Analyst Trick — Combine with VALUE()
If you extract numbers (like 2025) and plan to calculate with them:
=VALUE(MID(A2,5,4))
That converts the text “2025” into a real number you can use in formulas or charts.
⚙️ Common Mistakes
| Problem | Cause | Fix |
|---|---|---|
| Wrong characters extracted | Miscounted start/length | Use FIND() to locate exact position |
| Formula errors (#VALUE!) | Dash or symbol not present | Wrap in IFERROR() |
| Numbers not calculating | Still text | Wrap with VALUE() |
| Mixed-length codes | Hardcoded start/length | Use FIND + LEN combo |
📊 Copy-Paste Excel Demo Table
| A (Raw Data) | Task | Formula | Expected Result |
|---|---|---|---|
| INV-2025-001 | Extract Prefix | =LEFT(A2,3) | INV |
| INV-2025-001 | Extract Year | =MID(A2,5,4) | 2025 |
| INV-2025-001 | Extract Suffix | =RIGHT(A2,3) | 001 |
| SKU-UK-999 | Extract Region | =MID(A3,FIND("-",A3)+1,2) | UK |
| EMP-007-JS | Extract Last Letters | =RIGHT(A4,2) | JS |
❤️ Support XL4MULA — Free Learning, Powered by You
At XL4MULA, every video, sheet, and post is made to save you time — and teach Excel in plain English.
If this guide helped you, consider supporting future content with a voluntary donation. It keeps the tutorials free for everyone who’s learning.
👉 Visit XL4MULA.com to:
- Watch the 60-second LEFT/MID/RIGHT video 🎥
- Download the sample Excel sheet
- Explore all 20 Excel Cleaning Shorts
- And, if you’d like, leave a small donation ❤️
✅ Takeaway
| Function | Think of It As | Use It For |
|---|---|---|
| LEFT | From the start | Prefixes, codes, names |
| MID | From the middle | Years, IDs, categories |
| RIGHT | From the end | Suffixes, region codes |
Together, they’re Excel’s text extraction trio — clean, fast, and perfect for automating structured data cleanup.