MID Function in Excel: Pull Text From the Middle
By ExcelReady TeamUpdated 7 min read
The short answer
The MID function in Excel returns a set number of characters from the middle of text. Write =MID(text, start_num, num_chars): the text, the position to start at, and how many characters to take. For example, =MID("ORD-2024-0081",5,4) returns 2024. For text of varying length, use FIND or SEARCH to work out the start position.
Key takeaways
- The shape is =MID(text, start_num, num_chars); the first character is position 1.
- MID(A2,5,4) starts at character 5 and returns 4 characters.
- If num_chars runs past the end, MID just returns what is left, with no error.
- Use FIND or SEARCH to find a start position when the text varies in length.
- MID always returns text, so wrap it in VALUE for a number you can add up.
On this page+
The MID function in Excel pulls characters out of the middle of a cell. You tell it where to start and how many characters to take, so =MID("ORD-2024-0081",5,4) returns 2024. This guide is for anyone cleaning codes, IDs or imported text, and it covers fixed positions, variable positions with FIND, and the errors to watch for.
What does the MID function do?#
MID returns part of a text string, starting at any position you choose. The syntax has three parts:
=MID(text, start_num, num_chars)| Part | What it means | Example |
|---|---|---|
| text | The text or cell to read from | A2 |
| start_num | The position of the first character you want | 5 |
| num_chars | How many characters to return | 4 |
Positions start at 1, not 0. Spaces, dashes and punctuation all count as characters. MID works in every version of Excel, on Windows and Mac.
MID's siblings are LEFT, which takes characters from the start, and RIGHT, which takes them from the end. The overview of LEFT, RIGHT and MID compares all three. MID is the one you need when the part you want sits between other parts.
How to use MID with a fixed position#
When every value has the same layout, count the positions once and use fixed numbers. Take order IDs like ORD-2024-0081:
| Position | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Character | O | R | D | - | 2 | 0 | 2 | 4 | - | 0 | 0 | 8 | 1 |
The year starts at position 5 and is 4 characters long:
=MID(A2,5,4)The result is 2024. The order number starts at position 10 and is also 4 long, so =MID(A2,10,4) returns 0081.
If you're not sure how long a value is, LEN counts the characters for you: =LEN(A2) returns 13 here. The guide to counting characters with LEN shows more ways to use it.
What happens if num_chars is too big?#
Nothing breaks: MID returns whatever is left and stops at the end of the text. This makes a handy shortcut for "everything from position N onwards":
=MID(A2,10,100)This returns 0081, because only 4 characters remain after position 10. Using a large number like 100, or LEN(A2), means you don't need to calculate the exact length.
Two more edge cases are worth knowing:
| Formula | Result | Why |
|---|---|---|
=MID(A2,20,4) | (empty text) | Start is past the end of the text |
=MID(A2,0,4) | #VALUE! | Start must be 1 or more |
=MID(A2,5,-1) | #VALUE! | Length can't be negative |
How to use MID with FIND for variable lengths#
When the part you want moves around, let FIND work out the start position. FIND returns the position of one piece of text inside another. Say product codes have a region of any length, a dash, then a three-letter category: TX-ELC-40512 or CALIF-HOM-118.
To get the category, start one character after the first dash:
=MID(A2,FIND("-",A2)+1,3)| A2 | FIND("-",A2) | Start | Result |
|---|---|---|---|
| TX-ELC-40512 | 3 | 4 | ELC |
| CALIF-HOM-118 | 6 | 7 | HOM |
The +1 matters. Without it, MID starts on the dash itself and returns -EL.
FIND is case-sensitive. SEARCH works the same way but ignores case and allows wildcards. For finding a single character like a dash or @, either one is fine.
How to extract text between two characters#
To get text between two markers, use FIND twice: once for the start, once to work out the length. Say A2 holds Smith, John (Sales) and you want the department inside the brackets:
=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)| Piece | Value | Meaning |
|---|---|---|
FIND("(",A2) | 13 | Position of the opening bracket |
FIND(")",A2) | 19 | Position of the closing bracket |
| Start | 14 | One after the opening bracket |
| Length | 19 − 13 − 1 = 5 | Characters between the brackets |
The result is Sales. This pattern looks long, but it's always the same shape: start after the first marker, length is the gap between the markers minus one. There's a full walkthrough in extracting text between two characters, and the same idea pulls out a domain name in extracting the domain from an email address.
In Microsoft 365 or Excel 2024, TEXTAFTER and TEXTBEFORE can do this with less typing: =TEXTBEFORE(TEXTAFTER(A2,"("),")"). MID with FIND still works in every version, which matters in job tests that use older Excel.
Why does MID return a number I can't add up?#
MID always returns text, even when the result looks like a number. The year 2024 from the order ID is text: it sits on the left of the cell, and SUM ignores it.
To get a real number, wrap MID in VALUE:
=VALUE(MID(A2,5,4))Now the result is the number 2024, which you can compare, sort and calculate with. A quick alternative is =MID(A2,5,4)+0, but VALUE is clearer for anyone reading your formula later.
Common mistakes with MID#
Most MID mistakes come from counting positions wrong. Check these:
- Starting at 0. The first character is position 1.
MID(A2,0,4)returns #VALUE!. - Forgetting the +1 after FIND. Without it, the result includes the marker character.
- FIND can't find the marker. If a row has no dash, FIND returns #VALUE! and so does MID. Wrap it in IFERROR to show a blank or a message.
- Hidden spaces. A leading space shifts every position by one. Clean the column with TRIM first.
- Expecting a number. MID returns text. Use VALUE before maths or number lookups.
Practise it#
MID shows up in data-cleaning questions in many Excel job tests, often combined with FIND. Practise LEFT, RIGHT, MID and LEN in a live spreadsheet: you type the formula, and it checks your answer instantly. The MID function guide is a quick syntax reference, and the guide to splitting a full name into first and last puts these functions to work on real data.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Order IDs follow the pattern ORD-2024-0081. Click B2 and use MID to pull out the 4-digit year, which starts at character 5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | |||
| 2 | ORD-2024-0081 | |||
| 3 | ORD-2023-1145 | |||
| 4 | ORD-2025-0007 | |||
| 5 | ORD-2024-0932 | |||
| 6 | ORD-2025-0410 | |||
| 7 | ||||
| 8 | ||||
| 9 |
Tip: Count the characters: O, R, D and the dash take positions 1 to 4, so the year starts at 5 and is 4 characters long.
Practise this in a lesson
Frequently asked questions
What does the MID function do in Excel?
MID returns characters from the middle of a text string. You give it the text, the position to start at and how many characters to return, as in =MID(A2,5,4).
How do I use MID with a variable length?
Use FIND or SEARCH to calculate the start position or the number of characters. For example, =MID(A2,FIND("-",A2)+1,3) returns the three characters after the first dash, wherever it is.
Why does MID return #VALUE!?
The start position is less than 1, or the number of characters is negative. This often happens when FIND can't find its character, or when a calculated length goes below zero.
How do I convert the result of MID to a number?
MID always returns text. Wrap it in VALUE, as in =VALUE(MID(A2,5,4)), or add zero with =MID(A2,5,4)+0, to get a real number.
What is the difference between MID and MIDB?
MID counts every character as one. MIDB counts bytes, which only differs for some double-byte languages such as Japanese, Chinese and Korean. For most work, use MID.