MID returns a specific number of characters from the middle of a text string, starting at a position you choose. It is ideal for extracting codes, initials, or segments from structured text.
=MID(text, start_num, num_chars)What does MID do?
MID is a Text function that extracts a substring from the middle of a text value. You tell it where to start and how many characters to take, and it returns exactly that slice. For example, =MID("INV-2024-001",5,4) returns 2024.
Because office data often packs several pieces of information into one cell, MID is a practical way to split codes, IDs, and labels without retyping. It works well with FIND or SEARCH when the start position is not fixed.
Imagine a column of product codes like AB-1234-XY. You need the four-digit number in the middle for a lookup. MID with a start of 4 and a length of 4 gives you 1234 in one formula, ready to copy down the column.
When to use MID
- Extract a year or month from a structured reference code such as
INV-2024-07. - Pull the middle digits from a product or part number for matching.
- Get initials or a short label from a full name or description.
- Isolate a region code embedded in an account ID.
- Remove unwanted prefixes and suffixes from imported text.
MID syntax
=MID(text, start_num, num_chars)| Argument | Required? | What to enter |
|---|---|---|
text | Required | The text string or cell reference you want to extract from. Example: A2 or "INV-2024-001". |
start_num | Required | The position of the first character to extract. The first character in the text is 1. Example: 5 starts at the fifth character. |
num_chars | Required | The number of characters to return from the start position. Example: 4 returns four characters. |
What it returns: MID returns a text string containing the requested characters. If start_num is greater than the text length, it returns an empty string. If num_chars is larger than the remaining characters, it returns all characters from start_num to the end.
MID examples
1 Extract a year from an invoice code
The goal is to pull the four-digit year from each invoice code. The formula =MID(A2,5,4) starts at the fifth character, where the year begins after INV-, and takes four characters. For INV-2024-001 it returns 2024. Copying the formula down works because every code follows the same pattern.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | InvoiceCode | |||
| 2 | INV-2024-001 | 2024 | ||
| 3 | INV-2023-045 | |||
| 4 | INV-2024-112 | |||
| 5 | INV-2022-008 | |||
| 6 | INV-2024-076 | |||
| 7 | INV-2023-031 | |||
| 8 |
2 Get the middle digits of a product code
Product codes look like AB-1234-XY, and you need the four-digit block. =MID(A2,4,4) starts at the fourth character (after AB-) and returns four characters. For AB-1234-XY the result is 1234. This is useful when the middle digits are the key for a lookup or report.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ProductCode | |||
| 2 | AB-1234-XY | 1234 | ||
| 3 | CD-5678-ZW | |||
| 4 | EF-9012-QR | |||
| 5 | GH-3456-MN | |||
| 6 | IJ-7890-OP | |||
| 7 | KL-2345-ST | |||
| 8 |
3 Pull a region code from an account ID
Account IDs are built as ACC-NE-1001, where NE is the region. =MID(A2,5,2) starts at the fifth character and returns two characters. For ACC-NE-1001 it returns NE. This lets you group or filter records by region without splitting the column manually.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | AccountID | |||
| 2 | ACC-NE-1001 | NE | ||
| 3 | ACC-SW-1002 | |||
| 4 | ACC-MW-1003 | |||
| 5 | ACC-WE-1004 | |||
| 6 | ACC-SE-1005 | |||
| 7 | ACC-NE-1006 | |||
| 8 |
4 Isolate a month from a date-like reference
Reference codes like 2024-07-REP contain the month in positions 6 and 7. =MID(A2,6,2) starts at the sixth character and returns two characters. For 2024-07-REP it returns 07. This is handy when you need a month label for sorting or reporting.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reference | |||
| 2 | 2024-07-REP | 07 | ||
| 3 | 2024-08-REP | |||
| 4 | 2023-12-REP | |||
| 5 | 2024-01-REP | |||
| 6 | 2024-03-REP | |||
| 7 | 2023-11-REP | |||
| 8 |
Common MID errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | start_num or num_chars is not a number, for example a text value or an empty cell reference. | Make sure both arguments are numbers or refer to cells containing numbers. Use VALUE if needed. |
| #VALUE! | start_num is less than 1. | Set start_num to 1 or greater. The first character is position 1, not 0. |
| #VALUE! | num_chars is negative. | Use zero or a positive number. If you want no characters, use 0. |
| Empty result | start_num is greater than the length of the text. | Check the text length with LEN and adjust start_num. MID returns an empty string in this case. |
Tips for using MID
- Use
FINDorSEARCHto calculate start_num when the position varies between rows. - Remember that spaces count as characters, so
MID("AB CD",3,2)returnsC. - Wrap MID in
VALUEif you need the extracted text as a number for calculations. - Test on one row first, then fill down and spot-check a few results.
- Combine MID with
LENto extract everything after a certain point:MID(A2,5,LEN(A2)).
Frequently asked questions
What is the difference between MID, LEFT, and RIGHT?
LEFT takes characters from the start, RIGHT takes them from the end, and MID takes them from a position you specify in the middle.
Can MID extract numbers from text?
MID returns text, even if the characters are digits. Wrap it in VALUE to convert the result to a number.
Why does MID return an empty cell?
If start_num is greater than the length of the text, MID returns an empty string. Check the text length and the start position.
Does MID work with dates?
Dates are stored as numbers, so MID on a date cell may not give the expected text. Convert the date to text first with TEXT if you need to extract parts.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. MID reference