%20RIGHT()%20MID().jpg)
In the world of Excel, handling text data is just as important as working with numbers. Whether you’re cleaning up messy imports, pulling specific codes from IDs, or standardizing name formats, text extraction becomes crucial.
That’s where Excel’s LEFT(), RIGHT(), and MID() functions come into play. These functions allow you to extract parts of a text string, and are extremely useful for data cleaning, analysis, and reporting.
In this blog, we’ll break down:
-
What these functions do
-
Their syntax
-
Use-case examples
-
Tips and combinations for advanced usage
📌 1. LEFT() – Extract from the Start
🔹 Purpose:
The LEFT() function is used to extract characters from the beginning (left side) of a text string.
🔹 Syntax:
-
text: The string or cell reference. -
num_chars: Number of characters you want to extract.
✅ Example:
If A1 contains:
"EMP-23456"
Then:
Result: "EMP"
This is useful for extracting prefixes, country codes, or standard labels from text.
📌 2. RIGHT() – Extract from the End
🔹 Purpose:
The RIGHT() function pulls out characters from the end (right side) of a string.
🔹 Syntax:
✅ Example:
From A1 = "EMP-23456"
Result: "23456"
Great for retrieving ID numbers, codes, or suffixes.
📌 3. MID() – Extract from the Middle
🔹 Purpose:
MID() lets you extract a substring from any position within a text string — not just the beginning or end.
🔹 Syntax:
-
start_num: The position (starting from 1) where extraction begins. -
num_chars: Number of characters to extract.
✅ Example:
From A1 = "EMP-23456"
Result: "23456"
It started from position 5 and pulled 5 characters — perfect for slicing middle content.
🧠 Use Cases for Admins, MIS, and Facilities
🔸 Extracting Department Codes:
Suppose "HR-MGR-8902" is stored in a cell (A2).
You want just "HR", the department code.
🔸 Pulling Employee ID:
From "HR-MGR-8902", extract "8902":
🔸 Getting the Role Code:
To extract "MGR" from the middle:
You start at character 4 (H-R--) and pull 3 characters.
🧪 Combining with FIND() or LEN() for Dynamic Formulas
🔹 Example 1: Extract First Name from Full Name
Let’s say A1 = "John Doe"
Result: "John"
This formula finds the space and extracts everything before it.
🔹 Example 2: Extract Last Name
Result: "Doe"
This formula finds the length after the space and pulls it from the right.
⚙️ Real-Life Application Examples
✅ Case 1: Product Codes
-
A2 = "PRD-99872"
-
Extract "PRD":
=LEFT(A2, 3) -
Extract ID:
=RIGHT(A2, 5)
✅ Case 2: Email Cleanup
-
A3 = "[email protected]"
-
Extract domain:
Result: "company.com"
✅ Case 3: Invoice Numbers
-
A4 = "INV2024-8745"
-
Extract year:
=MID(A4, 4, 4) -
Extract number:
=RIGHT(A4, 4)
🚨 Common Errors to Avoid
| Issue | Reason |
|---|---|
| #VALUE! | If num_chars is negative or non-numeric |
| Wrong character returned | Not counting spaces correctly or wrong start position |
| Formula not dynamic | Using fixed numbers instead of LEN() or FIND() |
Tip: Always double-check string positions using LEN() or FIND() to avoid logic errors.