MID function

MID extracts a specified number of characters from a text string, starting at a given position, and returns the resulting substring.

=MID(text, start_num, num_chars)

Generate a MID formula

Describe what you need. The generator will reach for MID where MID is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How MID reads its arguments
textrequiredstart_numrequirednum_charsrequiredMID →
ArgumentRequiredDescription
textRequiredThe source string (or cell reference) from which characters will be taken; non-text values are coerced to text, and errors are propagated.
start_numRequiredA positive integer indicating the position of the first character to return; values less than 1 cause a #VALUE! error.
num_charsRequiredThe number of characters to return; must be zero or a positive integer, otherwise #VALUE! is returned.

Returns

MID returns a text string of the length you requested (or shorter if the start position exceeds the source).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Extract the region name from a combined ID string

Order IDRegionRepUnitsUnit PriceOrder Date
1001EastAlice5202023-01-15
1002WestBob3152023-02-20
=MID(B2 & "-" & A2, 1, 4)

Result: East

The formula concatenates the Region (B2) with a dash and the Order ID (A2) producing "East-1001". MID then starts at character 1 and returns the next four characters, which are "East". This demonstrates how MID can pull a fixed-length prefix from a dynamically built string.

2. Pull the month portion from an order date

Order IDRegionRepUnitsUnit PriceOrder Date
1001EastAlice5202023-01-15
1002WestBob3152023-02-20
=MID(F2, 6, 2)

Result: 01

Order dates are stored as text in the form YYYY-MM-DD. Starting at position 6 (the first digit of the month) and taking two characters yields "01" for January. Changing the row reference will return the appropriate month for any date in the log.

3. Get the first-letter initial of a sales rep

Order IDRegionRepUnitsUnit PriceOrder Date
1001EastAlice5202023-01-15
1003NorthCarol10122023-03-05
=MID(C3, 1, 1)

Result: C

The Rep column (C3) contains the full name "Carol". By starting at character 1 and requesting a single character, MID returns the initial "C". This technique is handy for creating abbreviated labels or badge IDs.

Common errors

Which MID error are you seeing?
MID returned an error#VALUE!
Change the start_num to a positive integer, e.g., use 1 instead of 0.
#VALUE!
Provide a non-negative integer for num_chars, such as 3 instead of -2.
#N/A
Wrap the lookup in IFERROR or verify the lookup key exists before calling MID.
ErrorWhy it happensHow to fix it
#VALUE!The start_num argument is zero or negative; MID requires a start position of at least 1.Change the start_num to a positive integer, e.g., use 1 instead of 0.
#VALUE!The num_chars argument is negative; MID cannot return a negative number of characters.Provide a non-negative integer for num_chars, such as 3 instead of -2.
#N/AThe text argument itself evaluates to #N/A (commonly from a failed lookup), and MID propagates that error.Wrap the lookup in IFERROR or verify the lookup key exists before calling MID.

Tips and when to use something else

  • Use LEFT when you always need characters from the beginning of a string; it avoids calculating the start position.
  • Use RIGHT to pull characters from the end of a string without counting the total length first.
  • If you need to locate a delimiter dynamically, combine FIND with MID instead of hard-coding the start position.
  • When extracting text based on a separator (e.g., before or after a dash), consider TEXTBEFORE or TEXTAFTER, which are more expressive than MID.

Related functions

LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.RIGHTRIGHT returns the specified number of characters from the end of a text string, defaulting to one character if omitted.FINDFIND returns the numeric position of a substring within a text string, counting from a specified start point and respecting case sensitivity.TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.

Frequently asked questions

How does MID treat Unicode characters like emojis?
MID counts each Unicode code point as one character, even if it occupies two bytes internally. Therefore an emoji is a single character for MID, and the function will return it correctly if the position and length include it.
Can MID extract characters directly from a numeric cell?
Numeric values are coerced to text before MID operates, so =MID(12345,2,2) returns "23". If you need leading zeros, wrap the number in TEXT first to control formatting.
What is the maximum length of the string that MID can return?
Excel and Google Sheets limit text strings to 32,767 characters. MID will never return more than that, and if you request more characters than exist, it simply returns the available characters without error.
Why does MID sometimes give a blank result instead of an error?
When start_num is greater than the length of the source text, MID returns an empty string (“”). This is by design, allowing formulas to gracefully handle out-of-range positions without throwing an error.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator