How to Use LEFT, RIGHT, and MID to Extract Text in Excel
Introduction
You have a column of order codes like NY-ELEC-1042, and you need the region, category, and order number in separate columns. LEFT, RIGHT, and MID do exactly that. Each one pulls characters from a different part of the text.
The Dataset
Here are the order codes we'll work with. Each one follows the same pattern: region, category, order number, separated by hyphens.
| A | |
|---|---|
| 3 | Order Code |
| 4 | NY-ELEC-1042 |
| 5 | SF-FURN-2187 |
| 6 | CH-TECH-3901 |
| 7 | NY-FURN-4520 |
| 8 | SF-ELEC-5003 |
We want each piece in its own column.
LEFT
LEFT returns characters from the start of a cell. The syntax is:
=LEFT(text, [num_chars])
- text: The cell or string to extract from.
- num_chars: How many characters to return. Defaults to 1 if omitted.
To get the two-letter region code, enter this in B4:
=LEFT(A4, 2)
The result is NY. Copy the formula down and you get SF, CH, NY, SF for the rest.
RIGHT
RIGHT does the opposite: it returns characters from the end. The syntax is:
=RIGHT(text, [num_chars])
- text: The cell or string to extract from.
- num_chars: How many characters to return. Defaults to 1 if omitted.
To get the four-digit order number in C4:
=RIGHT(A4, 4)
The result is 1042. The remaining rows give you 2187, 3901, 4520, and 5003.
MID
MID lets you start from any position, not just the beginning or end. The syntax is:
=MID(text, start_num, num_chars)
- text: The cell or string to extract from.
- start_num: The position to start at (1 means the first character).
- num_chars: How many characters to return.
The category starts at position 4 (right after "NY-") and is always 4 characters long. In D4:
=MID(A4, 4, 4)
The result is ELEC, with FURN, TECH, FURN, and ELEC for the rest.
The Full Result
After copying all three formulas down:
| A | B | C | D | |
|---|---|---|---|---|
| 3 | Order Code | Region | Order # | Category |
| 4 | NY-ELEC-1042 | NY | 1042 | ELEC |
| 5 | SF-FURN-2187 | SF | 2187 | FURN |
| 6 | CH-TECH-3901 | CH | 3901 | TECH |
| 7 | NY-FURN-4520 | NY | 4520 | FURN |
| 8 | SF-ELEC-5003 | SF | 5003 | ELEC |
One formula per column, and the codes are fully broken down. From here you could filter by region, count orders per category, or sort by order number.
Handling Variable-Length Text
What about text that isn't always the same length? Suppose you have names and want just the first name:
| A | |
|---|---|
| 3 | Full Name |
| 4 | John Smith |
| 5 | Alexandra Jones |
| 6 | Li Wei |
"John" is 4 characters, "Alexandra" is 9, and "Li" is 2. LEFT with a fixed number won't work for all three.
The FIND function handles this. It locates a specific character in a cell, so you can calculate where to split:
=LEFT(A4, FIND(" ", A4) - 1)
FIND(" ", A4) returns the position of the space (5 for "John Smith"). Subtract 1 and LEFT grabs everything before it. The results: John, Alexandra, Li.
Key takeaway: Pair LEFT, RIGHT, or MID with FIND when your data doesn't follow a fixed width.
Conclusion
LEFT for the start, RIGHT for the end, MID for anything in between. If your text lengths vary, pair them with FIND to calculate positions on the fly.
For a deeper look at FIND and its case-insensitive counterpart SEARCH, see our guide on FIND and SEARCH. For fixing capitalization, check out our guide on PROPER, UPPER, and LOWER.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.