MID Function in Excel, Google Sheets, Airtable & Notion
MID returns a number of characters starting at a given position. MID("INV-2024-0042", 5, 4) is “2024”: start at the 5th character and take 4.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | MID(text, start_num, num_chars) |
| Google Sheets | ✔ Built in | MID(string, starting_at, extract_length) |
| Airtable | ✔ Built in | MID(string, whereToStart, count) |
| Notion | ✔ Built in | substring(text, start - 1, start - 1 + count)Notion counts from 0 and the end position is exclusive, so Excel's MID(t, 5, 4) is substring(t, 4, 8). |
| Excel | =MID("INV-2024-0042", 5, 4) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =MID("INV-2024-0042", 5, 4) | open sheets.new ↗ |
| Airtable | MID("INV-2024-0042", 5, 4) | |
| Notion | substring("INV-2024-0042", 4, 8) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Arguments
| Argument | What it means |
|---|---|
text | The text to extract from. |
start_num | Position of the first character to return; the first character is 1. Past the end returns empty text; below 1 is #VALUE!. |
num_chars | How many characters to return. Negative is #VALUE!. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=MID("INV-2024-0042", 5, 4) | 2024 | Characters 5 to 8. | |
=MID(A2, FIND("@", A2) + 1, LEN(A2)) | Email domain | Everything after the @. A count longer than what is left just returns the rest. | |
=MID("abc", 10, 2) | (empty text) | Starting past the end returns empty text, not an error. | |
=MID("abc", 0, 2) | #VALUE! | Positions start at 1. |
Examples use Excel / Google Sheets syntax unless noted.
How MID works
MID covers anything LEFT and RIGHT can't: a year inside a code, a middle segment of a SKU, the text between two markers. Pair it with FIND to make the start and length dynamic: =MID(A2, FIND("(", A2) + 1, FIND(")", A2) - FIND("(", A2) - 1) returns what is inside the brackets.
To take “everything from position n onward”, pass a generous count such as LEN(A2); MID never fails because the count runs past the end. It does fail with #VALUE! when the start is below 1, which usually means a FIND returned an unexpected position.
Airtable's MID uses the same 1-based positions as Excel. Notion's substring(text, start, end) is 0-based and takes an end position rather than a length, so subtract 1 from the start and add the count to get the end. Getting this wrong is the usual cause of formulas that are off by one character when moved to Notion.
Frequently Asked Questions
- How do I extract text between two characters?
- Find both positions and take what lies between:
=MID(A2, FIND("[", A2) + 1, FIND("]", A2) - FIND("[", A2) - 1). In Excel 365TEXTBEFORE(TEXTAFTER(A2, "["), "]")is simpler.