Excel MID Function

MID extracts a specific number of characters from a text string, starting at a position you choose. It returns the extracted characters as text, even if they look like numbers. That last part trips up a lot of beginners: if you MID out "2026" from a date code, you get the text "2026," not the number 2026, and formulas that expect a number will choke on it.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and every earlier version back to Excel 97. MID is not a newer function, and its behavior hasn't changed across versions.

Syntax

=MID(text, start_num, num_chars)

Parameter	Required	Description
text	Yes	The string you're pulling characters from. Can be a literal string in quotes or a cell reference.
start_num	Yes	The character position to start at, counting from 1. Position 1 is the first character in the string, not the first character after any leading space.
num_chars	Yes	How many characters to pull, starting from start_num. If fewer characters remain than you asked for, MID just returns what's left instead of erroring.

Basic Example

Say you're working with employee IDs formatted like EMP-2024-0451, and you need to pull out the four-digit year in the middle.

=MID(A2, 5, 4)

// A2  = the full ID, "EMP-2024-0451"
// 5   = start at the 5th character, right after "EMP-"
// 4   = grab 4 characters

MID counts to the 5th character in the string, which is the "2" in "2024," then extracts four characters from there. It returns "2024" as text. If you later need it as a real number for a calculation, wrap it: =VALUE(MID(A2, 5, 4)).

How MID Works

Position counts from 1, not 0

The first character in the string is position 1. In "EMP-2024-0451," the "E" is position 1, the first hyphen is position 4, and the "2" that starts the year is position 5. Count carefully when a string has leading spaces; the space itself occupies position 1.

The result is always text

MID never returns a number, even when the extracted characters are all digits. This matters the moment you try to use the result in a SUM, a date calculation, or a comparison against a number. Wrap the formula in VALUE() (or add zero to it) whenever the downstream formula needs an actual number:

=VALUE(MID(A2, 5, 4))   // returns the number 2024, not the text "2024"

Overshooting num_chars doesn't cause an error

If you ask MID for more characters than remain in the string, it simply returns everything left over rather than throwing an error. This is genuinely useful: instead of calculating the exact remaining length, you can hard-code a number that's "big enough," like 255, and let MID stop naturally at the end of the string.

=MID(A2, 5, 255)   // returns everything from position 5 to the end, however long that is

Fixed positions only work when your data is consistent

MID on its own assumes every row has the same structure: the year always starts at position 5, the delimiter is always in the same spot. That breaks the moment one row is formatted differently. For text where the position you need shifts row to row, pair MID with FIND or SEARCH, covered in the use cases below.

Common Use Cases

Extracting a first name from a full name

Full names don't have a fixed length, so you can't hard-code a start position. Use FIND to locate the space, then feed that position into MID.

=MID(A2, 1, FIND(" ", A2) - 1)

// A2            = "Jordan Blake"
// FIND(" ", A2) = finds the space at position 7
// - 1           = stops one character before it, so the space isn't included

This returns "Jordan." FIND is case-sensitive and looks for an exact character match; SEARCH does the same job but ignores case, which matters if you're matching text delimiters instead of a fixed character like a space.

Pulling the username out of an email address

The part before the "@" symbol is variable length, so this is the same pattern applied to a different delimiter.

=MID(B2, 1, FIND("@", B2) - 1)   // returns "jblake" from "jblake@acme.com"

Extracting text between two parentheses

Product codes and reference numbers sometimes bury a value inside parentheses, like "Widget (SKU-4471)." Two SEARCH calls locate the opening and closing parentheses, and MID grabs everything in between.

=MID(C2, SEARCH("(", C2) + 1, SEARCH(")", C2) - SEARCH("(", C2) - 1)

// SEARCH("(", C2) + 1                          = start right after the open paren
// SEARCH(")", C2) - SEARCH("(", C2) - 1        = length of the text between the parens

Converting a text-formatted date into a real date

Systems that export dates as "20260713" (no separators) need to be split into year, month, and day before DATE can reassemble them.

=DATE(MID(D2, 1, 4), MID(D2, 5, 2), MID(D2, 7, 2))

// MID(D2, 1, 4)  = "2026", the year
// MID(D2, 5, 2)  = "07", the month
// MID(D2, 7, 2)  = "13", the day

DATE accepts these as numbers automatically, so you don't need to wrap each MID in VALUE here; DATE coerces text digits into numbers on its own.

Handling Errors

MID itself throws #VALUE! in a few specific situations, and the FIND or SEARCH functions it's commonly paired with throw #VALUE! and #N/A respectively when the delimiter you're searching for doesn't exist in the string.

Common causes of #VALUE!:

• start_num is less than 1
• num_chars is a negative number
• FIND or SEARCH, nested inside the MID formula, can't locate the delimiter character

Since the FIND/SEARCH-nested version is the one most likely to fail on real-world data (not every row has a space, an "@", or a matching parenthesis), that's the one worth wrapping. Plain MID with hard-coded numeric arguments won't throw an error if those arguments are valid, so wrapping it in IFERROR anyway just hides genuine problems elsewhere in the formula.

=IFERROR(MID(A2, 1, FIND(" ", A2) - 1), "No space found")

Notes & Gotchas

How do you use the MID function in Excel?

Give MID three things: the text to pull from, the character position to start at, and how many characters to grab. =MID("apple", 2, 3) starts at the second letter and takes three characters, returning "ppl." For real spreadsheet data, replace the literal string with a cell reference.

What is the formula for MID function in Excel?

The formula is =MID(text, start_num, num_chars). All three arguments are required; there's no optional argument to omit, unlike many other Excel functions. Get any one of the three wrong and you'll either get the wrong substring or an error.

What is the difference between LEFT, RIGHT, and MID functions in Excel?

LEFT extracts from the start of a string, RIGHT extracts from the end, and MID extracts from any position you specify in between. LEFT and RIGHT only need two arguments (text and number of characters) because their starting point is fixed. MID needs three because you have to tell it where in the middle to begin.

How do you extract text between two characters or delimiters in Excel?

Use SEARCH or FIND to locate each delimiter's position, then use the difference between those positions as the num_chars argument for MID. For a middle name sitting between two spaces, like "Jordan Alex Blake," nest a second SEARCH that starts scanning right after the first space, so it finds the second space instead of matching the first one again.

=MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + 1) - SEARCH(" ", A2) - 1)

// SEARCH(" ", A2)                              = first space, position 7
// SEARCH(" ", A2, SEARCH(" ", A2) + 1)          = second space, searched starting after the first
// the difference between the two, minus 1      = length of the middle name

Why does the MID function return a #VALUE! error?

MID returns #VALUE! when start_num is less than 1 or when num_chars is negative. It does not error when start_num is larger than the length of the text; in that case it just returns an empty string. It also returns an empty string, not an error, when num_chars is 0.

Does MID work with emoji or double-byte characters?

Standard MID counts characters, not bytes, so it handles most double-byte languages (Japanese, Chinese, Korean) correctly on a character-by-character basis. Emoji and certain symbols stored as surrogate pairs can still throw off the count, since Excel may treat one visible emoji as two character units. If you're working with East Asian text and need byte-based counting instead, MIDB is the byte-aware equivalent.

Related Functions

Function	Use this when...
FIND	You need the exact position of a character and want case-sensitive matching.
SEARCH	You need the position of a character or word and want to ignore case.