The Excel MID function, explained interactively.
MID returns num_chars characters of a text value, starting at position start_num. Unlike LEFT and RIGHT, MID can cut from anywhere — which is exactly what you want for structured strings like ISO timestamps, phone numbers, and compound SKUs where the interesting field sits in the middle.
How to use MID
- Type
=MID(and supply the text — a cell reference, a literal string, or a formula that returns text. - Add a comma and start_num — the 1-based position of the first character to return.
1means the first character;6is the first digit of the month in an ISO timestamp. - Add num_chars — how many characters to take. MID silently clamps when the request runs past the end; it never errors for over-long reads.
- Close the parenthesis. For dynamic start points, pair with
FIND:=MID(A2, FIND("@", A2) + 1, 99)extracts the domain from an email.
Move the demo’s start_num and num_chars pickers — (6, 2) extracts the month from an ISO timestamp, (4, 3) grabs the area code from a phone number.
num_chars characters of a text value, starting at position start_num. Indexing is 1-based; positions past the end return empty, never error.1 is the first character. Values greater than the text length return empty. Values less than 1 return #VALUE!.start_num. 0 returns empty. MID silently clamps when the request runs past the end — no padding, no error.| A | B | |
|---|---|---|
| 1 | Source | MID (start=6, n=2) |
| 2 | 2026-04-19T13:25:00Z | 04 |
| 3 | 2025-11-30T08:02:47Z | 11 |
| 4 | +1-415-555-2041 | 5- |
| 5 | +1-212-555-0173 | 2- |
| 6 | NYC-2025Q2-12045 | 02 |
| 7 | 978-0-13-468599-1 | -1 |
=MID(A2, 6, 2). start_num = 6, num_chars = 2: from an ISO timestamp this extracts the month (characters 6–7). For the phone rows it reaches into the dash-and-area-code region, for the SKU it lands on the year digits. Other B cells show what B3:B7 would return if you filled the formula down.MID syntax and arguments
Three required arguments — no defaults, no optionals. See Microsoft’s official MID / MIDB reference for the canonical specification.
A2), literal ("2026-04-19"), or any formula that resolves to text. Numeric inputs are coerced to text via General formatting before extraction.1 is the first character. Values greater than the text length return an empty string. Values less than 1 return #VALUE!.start_num. 0 returns an empty string. Values that run past the end of the text clamp silently — MID never pads and never errors for over-long reads. Negative values return #VALUE!.MID examples
Four patterns that cover nearly every real-world MID use.
Example 1: MID — extract month from an ISO timestamp
The canonical MID case — the month sits at a fixed, known position inside the standard ISO-8601 format.
Returns 04 from 2026-04-19T13:25:00Z. Characters 1–4 are the year, 6–7 the month, 9–10 the day, 12–13 the hour. Wrap the month output with VALUE if you need to use it in arithmetic.
Example 2: MID + FIND — extract after a delimiter
When the start position depends on content, let FIND compute it. Pulling the email domain is the classic case.
FIND returns the position of the @; +1 jumps past it; 99 is a comfortably big num_chars that MID will silently clamp to the remainder of the string. No error if the domain is shorter than 99 chars — that’s MID’s forgiving length behaviour doing the work.
Example 3: MID between two delimiters
Extract the segment between the first and second dashes — useful for multi-part SKUs or compound identifiers.
The inner FIND("-", A2, FIND(...)+1) finds the second dash; the arithmetic subtracts position of the first dash and the delimiter itself. In Excel 365, TEXTBEFORE(TEXTAFTER(A2, "-"), "-") reads much cleaner.
Example 4: MID — parse fixed-width records
Legacy systems still export fixed-width files where each field starts at a known column. MID is the idiomatic parser — one formula per column.
Takes a 10-character field starting at column 11, then strips padding spaces with TRIM. Pair with VALUE for numeric columns; chain several MID calls into TEXTJOIN to reshape fixed-width rows into delimited CSV.
Common MID errors and fixes
Four failure modes, each with what to check and how to recover.
MID returns #VALUE!
Cause: start_num is less than 1, or num_chars is negative. MID treats both as invalid.
Guard computed positions with MAX(1, start_expr) and MAX(0, n_expr). For upstream errors (text argument is itself #N/A), wrap with IFERROR.
MID returns empty string unexpectedly
Cause: start_num is past the end of the text. MID returns "", not an error — can silently propagate to downstream formulas.
Check the source length with LEN(A2). If some rows really are too short, use IF(LEN(A2)>=start, MID(...), fallback) to make the empty case explicit.
MID with FIND returns #VALUE!
Cause: the delimiter doesn’t exist in the source, so FIND returns #VALUE! and poisons the MID call.
Wrap in IFERROR with a meaningful fallback: =IFERROR(MID(A2, FIND("@", A2)+1, 99), A2). Use SEARCH instead of FIND for case-insensitive matching (same error behaviour).
MID returns the wrong characters on CJK text
Cause: in legacy DBCS Excel builds, some characters occupy 2 bytes; MID counts characters while MIDB counts bytes. Mixing the two can offset the read.
In modern Unicode Excel (365 / 2021+), MID is character- accurate. For legacy or certain regional SKUs where byte positions matter, switch to MIDB. Never mix MID and MIDB in the same chain unless you’ve mapped out the byte-vs-character arithmetic.
MID vs LEFT, RIGHT & FIND
Four text-extraction tools. Pick by which end you’re cutting from and whether the split point is fixed or dynamic.
| Function | Cuts from | Args | Typical use |
|---|---|---|---|
| MID | Any position | text, start_num, num_chars | Month from ISO date, domain from email, field from fixed-width record |
| LEFT | Start of string | text, [num_chars] | Prefix, country code, ISO year |
| RIGHT | End of string | text, [num_chars] | File extension, last N digits, area tail |
| FIND | (locator, not extractor) | find_text, within_text, [start] | Pair with MID / LEFT / RIGHT to compute dynamic positions |
Rule of thumb: LEFT cuts from the front, RIGHT cuts from the back, MID cuts from anywhere. FIND (case-sensitive) and SEARCH (case-insensitive) locate the positions that feed into any of the three. For repeated splits on the same delimiter, Excel 365’s TEXTSPLIT, TEXTBEFORE, and TEXTAFTER usually read cleaner than nested MID / FIND chains.
MID frequently asked questions
6.01What does MID do when start_num is past the end of the text?▸
It returns an empty string — MID silently returns "" when start_num exceeds the text length, no error. That makes MID safe inside larger formulas where the source might sometimes be shorter than expected.
6.02How do I extract text between two delimiters with MID?▸
Combine MID with two FIND calls: =MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1) - FIND("-", A2) - 1)returns whatever sits between the first and second dashes. In Excel 365, TEXTBEFORE, TEXTAFTER, or TEXTSPLIT are usually cleaner.
6.03Why does MID start at 1, not 0?▸
MID follows Excel’s user-facing indexing convention — positions are 1-based so they match the characters you count by eye. Passing 0 or a negative start_num returns #VALUE!. This is consistent with FIND, SEARCH, LEFT (character 1 = first character), and REPLACE.
6.04Does MID work on numbers?▸
Yes — MID first coerces the number to text using General formatting, then extracts. MID(12345, 2, 2) returns the text "23", not the number 23. Wrap with VALUE if you need the result back as a number for arithmetic.
6.05What’s the difference between MID and MIDB?▸
MID counts characters; MIDB counts bytes. They behave identically on single-byte text. On double-byte text (CJK, certain emoji) MIDB advances two bytes per character — so MID(A2, 2, 1) takes one character while MIDB(A2, 2, 1) takes half a character. In modern Unicode Excel, MID is almost always what you want.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.