Excel MID function
MID returns characters from the middle of a text value. Product serials, ID fragments, and fixed-format codes all use the same three arguments: the text, where to start, and how many characters to keep. The first character is position 1.
MID function syntax and its three arguments
MID returns a segment from the middle of text. start_num is the first character to keep, counting from 1. num_chars is how many characters to return. MID("PRD-1023-A", 5, 4) is "1023".
The source text, usually a cell reference (A2). A literal works too: MID("PRD-1023-A", 5, 4) returns 1023.
Where to start, counting from 1. Character 5 of PRD-1023-A is the first digit of the serial. 0 or a negative number returns #VALUE!.
How many characters to take. Four characters from position 5 are characters 5 through 8.
MID example: characters 5–8 of a product code
This MID example pulls the four-digit serial out of a fixed-format code.
| Product code | Start | Characters |
|---|---|---|
| PRD-1023-A | 5 | 4 |
| SRV-8841-B | 5 | 4 |
| ACC-0050-C | 5 | 4 |
This MID example returns 1023 for PRD-1023-A. Characters 1–3 are the category, character 4 is the dash, and characters 5–8 are the serial. MID("PRD-1023-A", 5, 4) is "1023". SRV-8841-B is 8841. ACC-0050-C is 0050 — the leading zero stays, because MID returns text.
4 Excel MID function gotchas
1. The start position is 1, not 0
The first character is position 1. MID(A2, 1, 3) matches LEFT(A2, 3). MID(A2, 0, 4) returns #VALUE!, and so does a negative start.
2. A start past the end is blank
MID("PRD", 9, 2) returns an empty string, not an error. The text has no character 9. When a blank result is surprising, check the length with LEN.
3. A negative length is #VALUE!
num_chars must be 0 or greater. A negative length returns #VALUE!. If you ask for more characters than remain, MID returns the rest: MID("PRD-1023-A", 8, 10) is "3-A".
4. MID counts characters, MIDB counts bytes
Spaces and dashes each take one position, so they shift everything after them. MID counts characters. MIDB counts bytes and is the double-byte variant. Fixed-format codes belong in MID.
Excel vs Google Sheets
Microsoft Excel (365 / Desktop)
Excel MID takes three arguments in desktop Excel and Microsoft 365. MIDB is the byte-count variant. Cross-sheet references work like any other function: =MID(Sheet2!A2, 5, 4).
Google Sheets
Google Sheets uses the same MID(text, start_num, num_chars) syntax. MID("PRD-1023-A", 5, 4) is 1023 there too. The start position is 1-based in both.
MID exercise for this guide
MID exercise
Extract the middle serial with MID. The formula has to follow A2 — a hardcoded 1023 will fail the next case.
FAQ
How does the Excel MID function work?
=MID(text, start_num, num_chars) returns num_chars characters from text, beginning at start_num. Positions start at 1. =MID("PRD-1023-A", 5, 4) returns "1023".
What is the MID formula in Excel?
The MID formula takes three arguments: the text, the starting position, and how many characters to return. =MID(A2, 5, 4) pulls characters 5 through 8 of A2.
What is the difference between MID, LEFT, and RIGHT?
LEFT takes characters from the start. RIGHT takes them from the end. MID takes them from any position in the middle. LEFT(A2, 3) returns the same text as MID(A2, 1, 3).
Why does MID return #VALUE! or a blank?
A start position below 1, or a negative character count, returns #VALUE!. A start past the last character returns an empty string. If the count runs past the end, MID returns whatever characters remain.