Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Text

MID

DEEL in Dutch Excel

Returns a piece from inside a text: you give the position where it starts and how many characters it runs.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Entry no.	Date	Entry year
2	VRK-2025-0412	30 Dec 2025	2025=MID(A2, 5, 4)=MID(A2; 5; 4)
3	INK-2025-1187	31 Dec 2025
4	MEM-2026-0003	2 Jan 2026
5	BNK-2025-0955	3 Jan 2026

Result: 2025

=MID(A2, 5, 4)

=MID(A2; 5; 4)

What MID does and when to use it

What it does: it takes a piece out of a text, starting at a position you choose, for example the booking year from an entry number.

When to use it: for fields at a fixed place in a code, or for the text between two delimiters when FIND supplies the start and the length. In Excel 2024 or Microsoft 365, TEXTBEFORE and TEXTAFTER read more clearly when delimiters bound the piece, and TEXTSPLIT cuts a text into many parts at once.

How Excel evaluates it

Excel works through the text in this order:

it numbers the characters of the text from 1
it reads start_num: below 1 is #VALUE!, beyond the last character gives empty text
it reads num_chars and checks it is not negative
it copies characters from start_num onwards, stopping after num_chars characters or at the end of the text, whichever comes first

The result is text, even when every character is a digit.

Watch out: FIND returns the position of the delimiter itself, so the text after it starts at FIND(...) + 1. Without the plus one the result is shifted by one.

Mental model

A ruler laid along the text: you put your finger on the start mark and read off the given number of characters; if the text ends first, you read what is there.

Syntax

Official MID documentation

MID(text, start_num, num_chars)

MID(text; start_num; num_chars)

Arguments
Argument	Type	Meaning
`text`	Required	The text to take a piece from; usually a cell reference.
`start_num`	Required	Position of the first character you want. The first character is 1.
`num_chars`	Required	How many characters to take. More than are left is allowed.

Return value:Text: num_chars characters starting at start_num

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 16 KB)

Beginner

Read the booking year from entry numbers

In the XAF auditfile export, every entry number is built as journal, year and serial number, like VRK-2025-0412. For the cut-off test around 31 December 2025 you want a column with the year from the entry number, to compare with the posting date. Every entry number has the same layout.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Entry no.	Date	Entry year
2	VRK-2025-0412	30 Dec 2025	2025=MID(A2, 5, 4)=MID(A2; 5; 4)
3	INK-2025-1187	31 Dec 2025
4	MEM-2026-0003	2 Jan 2026
5	BNK-2025-0955	3 Jan 2026
6

Formula cell (C2)Input

Formula in C2

=MID(A2, 5, 4)

=MID(A2; 5; 4)

Result: 2025

Step by step

Excel reads text: A2 holds VRK-2025-0412, 13 characters. V is 1, R is 2, K is 3, the first hyphen is 4, the year runs from 5 to 8, the second hyphen is 9, and 0412 is 10 to 13.
It reads start_num: 5. That is the 2 of 2025. The count starts at 1, so position 5 is the fifth character, not the sixth.
It reads num_chars: 4. From position 5 it takes 5, 6, 7 and 8: 2, 0, 2, 5.
MID returns the text 2025, and C2 shows 2025, aligned left because it is text.
Copy C2 down to C5: 2025, 2025, 2026, 2025. In row 5 the entry number says 2025 but the date is 3 January 2026, so that is an entry to look at in the cut-off test.
Change 5 to 4 in the formula and C2 shows -202: a start that is one too early takes in the hyphen and drops the last digit.

Watch out: If a journal code has four letters, such as MEMO-2026-0004, the year shifts one place and MID returns -202. A fixed start position is only safe while the layout really is fixed; check the journal codes in the export first.

More on this example: why it works, 1 variation

Why it works

Every entry number has the same layout, so the year always occupies positions 5 to 8. MID needs exactly that information: where to start and how far to go. LEFT and RIGHT cannot do this in one step, because the year touches neither end of the entry number.

Variations

=VALUE(MID(A2, 5, 4)) = YEAR(B2)

=VALUE(MID(A2; 5; 4)) = YEAR(B2)

Turns the year into a number and compares it with the year of the posting date: TRUE when they agree, FALSE for the entries to follow up in the cut-off test.

Intermediate

Take the supplier name after the entry code

In an export of purchase ledger lines, the description column starts with a seven-character entry code, a space, then the supplier name. The names differ in length, and a few have trailing spaces from the client's system. You want the name alone, clean, so you can match it with the creditors list.

Sheet excerpt: C4 holds the formula
Row	A	B	C
1	Description	Debit	Supplier
2	25-0412 Hoekstra Vastgoed BV	€4,500.00€ 4.500,00
3	25-0413 Lease Noord BV	€1,250.00€ 1.250,00
4	25-0414 Dekker Klimaatbeheer BV	€380.50€ 380,50	Dekker Klimaatbeheer BV=TRIM(MID(A4, 9, 100))=TRIM(MID(A4; 9; 100))
5	25-0415 De Vries Schoonmaak	€912.00€ 912,00
6

Formula cell (C4)InputMarks: extra spaceNote: A4 ends with two spaces: 25-0414 Dekker Klimaatbeheer BV followed by two spaces is 33 characters.

Formula in C4

=TRIM(MID(A4, 9, 100))

=TRIM(MID(A4; 9; 100))

Result: Dekker Klimaatbeheer BV

Step by step

Excel evaluates MID(A4, 9, 100) first. A4 is 25-0414 Dekker Klimaatbeheer BV with two trailing spaces: 8 characters for the code and the space, 23 for the name, 2 spaces, 33 in total. The code 25-0414 fills positions 1 to 7 and the space after it is 8, so the name starts at 9.
num_chars asks for 100 characters, but from position 9 only 33 - 8 = 25 are left. MID takes those 25 and stops at the end of the text without an error: Dekker Klimaatbeheer BV followed by two spaces.
TRIM removes the two trailing spaces, leaving Dekker Klimaatbeheer BV, 23 characters. C4 shows Dekker Klimaatbeheer BV.
Without TRIM, C4 would hold the name plus two invisible spaces, and an exact lookup in the creditors list would return #N/A.
Copied to C2, the same 9 and 100 return Hoekstra Vastgoed BV (20 characters left of the 100 asked for). In C3 they return Lease Noord BV. One oversized num_chars covers every name length.

Watch out: If a name could ever be longer than 100 characters, it is cut off without warning. Use LEN(A4) as num_chars when that is possible; it is always long enough.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The code has a fixed width, so the name always starts at position 9, and MID accepts a length longer than what is left. Asking for 100 is a deliberate way to say 'to the end' without calculating the length. TRIM then deals with the one complication, the padding some names carry.

More to watch out for

A code of six or eight characters breaks the fixed start of 9: you lose the first letter of the name or keep a space in front of it. TRIM hides the second case but not the first.

Variations

=TRIM(MID(A4, FIND(" ", A4) + 1, LEN(A4)))

=TRIM(MID(A4; FIND(" "; A4) + 1; LEN(A4)))

Starts after the first space instead of at a fixed 9, so codes of any length work.

Advanced

Extract the relation code between two separators

The client's system exports each journal line as one text field: entry number, relation code and journal, separated by vertical bars, like 25-0412|D1001|VRK. To match the lines with the debtors and creditors lists, you want the relation code between the first and the second bar. The second bar must be searched from after the first one, otherwise FIND lands on the first bar again.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Export line	Period	Relation
2	25-0412\|D1001\|VRK	33	D1001=MID(A2, FIND("\|", A2) + 1, FIND("\|", A2, FIND("\|", A2) + 1) - FIND("\|", A2) - 1)=MID(A2; FIND("\|"; A2) + 1; FIND("\|"; A2; FIND("\|"; A2) + 1) - FIND("\|"; A2) - 1)
3	25-0413\|C2001\|INK	33
4	25-0414\|D1017\|VRK	44
5	25-0415\|\|MEM	44
6

Formula cell (C2)Input

Formula in C2

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

=MID(A2; FIND("|"; A2) + 1; FIND("|"; A2; FIND("|"; A2) + 1) - FIND("|"; A2) - 1)

Result: D1001

Step by step

Excel evaluates the FIND functions first. FIND("|", A2) on 25-0412|D1001|VRK: 25-0412 fills positions 1 to 7, so the first bar is 8. This search occurs three times in the formula, and each returns 8.
The second FIND gets the start 8 + 1 = 9, so it skips the first bar. FIND("|", A2, 9): D1001 is 9 to 13, the second bar is 14. It returns 14.
start_num for MID is 8 + 1 = 9, the first character after the first bar.
num_chars is 14 - 8 - 1 = 5: the distance between the two bars, minus the first bar itself.
MID(A2, 9, 5) returns D1001, and C2 shows D1001.
Without the + 1 in the start of the second FIND, it starts at 8 and finds the first bar again. num_chars becomes 8 - 8 - 1 = -1, and MID returns #VALUE! because a length cannot be negative.
Copied to C3, 25-0413|C2001|INK gives the same positions, 8 and 14, and the result is C2001. In C5, 25-0415||MEM has its second bar at 9: num_chars is 9 - 8 - 1 = 0 and MID returns empty text, which the pitfalls explain.

Watch out: A line without a relation, such as 25-0415||MEM, returns empty text without any error, so the line looks fine at a glance. Count the empty results, for example with COUNTBLANK or a filter, before you rely on the match.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The piece you want is bounded by two delimiters, so MID needs the position of the first plus one as its start, and the distance between the two minus one as its length. The start_num argument of FIND is what makes the second bar the right one: by starting the search after the first bar, FIND cannot return the same bar twice.

More to watch out for

A line with fewer than two bars returns #VALUE!. That is useful as a signal, but wrap the formula in IFERROR if the sheet is shown to others.

Variations

=LET(
  firstBar, FIND("|", A2),
  secondBar, FIND("|", A2, firstBar + 1),
  MID(A2, firstBar + 1, secondBar - firstBar - 1)
)

=LET(
  firstBar; FIND("|"; A2);
  secondBar; FIND("|"; A2; firstBar + 1);
  MID(A2; firstBar + 1; secondBar - firstBar - 1)
)

The same with LET: the first bar is calculated once instead of three times, and the MID line reads as start after the first bar, length up to the second.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Keep only the digits of a typed invoice number

In your duplicate invoice test, the invoice numbers in the purchase ledger were typed in different ways: with prefixes, spaces, hyphens and slashes. To compare them you want the digits and nothing else, and a leading zero must survive. F-2025/0412 and inv 2025 0412 are then the same invoice, booked twice.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Invoice no. as typed	Supplier	Digits
2	F-2025/0412	Dekker Klimaatbeheer BV	20250412=LET( txt, A2, chars, MID(txt, SEQUENCE(LEN(txt)), 1), digits, FILTER(chars, ISNUMBER(--chars), ""), CONCAT(digits) )=LET( txt; A2; chars; MID(txt; SEQUENCE(LEN(txt)); 1); digits; FILTER(chars; ISNUMBER(--chars); ""); CONCAT(digits) )
3	inv 2025 0412	Dekker Klimaatbeheer BV
4	FA 0012-25	Tellinga Techniek BV
5	-	Noordhaven Logistiek
6

Formula cell (C2)Input

Formula in C2

=LET(
  txt, A2,
  chars, MID(txt, SEQUENCE(LEN(txt)), 1),
  digits, FILTER(chars, ISNUMBER(--chars), ""),
  CONCAT(digits)
)

=LET(
  txt; A2;
  chars; MID(txt; SEQUENCE(LEN(txt)); 1);
  digits; FILTER(chars; ISNUMBER(--chars); "");
  CONCAT(digits)
)

Result: 20250412

Step by step

txt is A2, F-2025/0412. LEN counts F, the hyphen, 4 digits, the slash and 4 digits: 1 + 1 + 4 + 1 + 4 = 11.
SEQUENCE(11) makes the list 1, 2, 3 up to 11. MID receives that whole list as start_num, with num_chars 1, and returns 11 single characters: F, -, 2, 0, 2, 5, /, 0, 4, 1, 2. That array is chars.
--chars tries to turn each character into a number. The digits succeed; F and the hyphen give #VALUE!, and the slash does too. ISNUMBER turns that into TRUE for the 8 digits and FALSE for the other 3.
FILTER keeps the characters where the test is TRUE: 2, 0, 2, 5, 0, 4, 1, 2. These are still the text characters from MID, not numbers, which matters in the next step. digits holds these 8.
CONCAT joins them into one text: 20250412. C2 shows 20250412, 8 characters.
Copied down, the other rows show how the formula behaves:
- inv 2025 0412 gives 20250412 as well, the same as C2 for the same supplier: a duplicate to follow up
- FA 0012-25 gives 001225 with its leading zeros, because the digits were never converted to a number
- the single hyphen has no digits, so FILTER returns its if_empty value, empty text, and C5 stays empty instead of showing #CALC!

Watch out: The mirror-image mistake is start_num and num_chars swapped: MID(txt, 1, SEQUENCE(LEN(txt))). It looks almost the same, but returns growing prefixes (F, F-, F-2 and so on) instead of single characters, and all of them fail the digit test for this row, because every prefix starts with the F.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

MID accepts an array as start_num and then returns an array, one piece per start position; with num_chars 1 that splits a text into its characters. Once the text is an array of characters, FILTER can apply any test per character and CONCAT puts the survivors back together.

Do not use this for a one-time cleanup: Find and Replace on spaces and hyphens, or Flash Fill, is quicker and leaves no formula behind. For a recurring import of thousands of ledger lines, a Power Query step with Text.Select that keeps only digits is faster than evaluating MID once per character per row, at the cost of a refresh step.

More to watch out for

Wrapping the result in VALUE to get a number drops the leading zeros: 001225 becomes 1225, and an invoice number that differs only in its leading zeros then looks identical. Keep invoice numbers as text.
The formula removes everything that is not a digit, including a typo such as the letter O instead of a zero. F-2025/O412 becomes 2025412, seven digits. Add a check such as LEN(C2) = 8 next to it.

Variations

=CONCAT(IF(ISNUMBER(--MID(A2, SEQUENCE(LEN(A2)), 1)), MID(A2, SEQUENCE(LEN(A2)), 1), ""))

=CONCAT(IF(ISNUMBER(--MID(A2; SEQUENCE(LEN(A2)); 1)); MID(A2; SEQUENCE(LEN(A2)); 1); ""))

Without LET and FILTER: IF replaces every non-digit with empty text, and CONCAT skips the empties. Longer, and it evaluates MID twice.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Result starts at the delimiter or misses a letter	Your result is shifted by one: it starts with the delimiter, or loses its first character.	Add 1 to the FIND result so the piece starts after the delimiter. Test with a short example and count the characters by hand once.
#VALUE! with a start position of 0	MID(A2, 0, 4) returns #VALUE!.	Positions start at 1 in Excel, not at 0 as in many programming languages. Use 1 for the first character.
SUMIFS on the extracted year finds nothing	You extracted a year with MID and a SUMIFS on it finds nothing.	The year is text. Either compare with text, or convert with VALUE so it matches the numbers elsewhere in the model.

Used in combinations

Find the customer from a bank descriptionCut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.Excel 2024 or later (This combination needs Excel 2024 or later)
Split a pasted list into a clean columnSplit a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.Excel 2024 or later (This combination needs Excel 2024 or later)