Knowledge base / Excel
MID in Excel: positions, lengths and delimiters
I extract an order suffix and text after a delimiter, explaining off-by-one mistakes, empty output and when TEXTAFTER is clearer.

I want to extract 099 from ORD-2024-099. This is a good use for MID: I know the fragment’s starting position and length. I begin by counting characters, because a formula shifted by one position can return perfectly valid text from the wrong place.
Syntax and availability
The English function name is MID; its Polish equivalent is FRAGMENT.TEKSTU. Microsoft’s documentation lists Microsoft 365 and Excel 2024, 2021, 2019, and 2016. In Polish formulas, use FRAGMENT.TEKSTU; ŚRODEK is not a valid name for this function.
=MID(text,start_num,num_chars)
The first argument is text or a cell reference. The second identifies the first character to return, counting from 1. The third gives a character count, not the final character’s position. Spaces and hyphens occupy positions as well.
These examples use English names and commas. The Polish version uses FRAGMENT.TEKSTU, SZUKAJ.TEKST, and DŁ with semicolons. The actual separator depends on regional settings. Translating the function name alone may not be enough when moving a formula between configurations.
A fixed position in an order identifier
Suppose A2 contains the synthetic identifier ORD-2024-099. ORD occupies positions 1 through 3, the first hyphen is position 4, the year occupies positions 5 through 8, and the next hyphen is position 9. The final number begins at position 10.
=MID(A2,10,3)
The result is the text 099. Starting at 9 would produce -09. Both formulas are syntactically valid, but only one answers our question. The leading zeroes remain part of the text, which matters for an identifier.
A fixed position is a reasonable assumption only when the format is fixed. If the prefix changes to ORDER, the same formula extracts another fragment. Before applying it to a column, I inspect a longer record, a shorter record, and one without the expected delimiter.
A few small examples
Each table row is an independent example, with the stated text in A2. The data is synthetic, and the results follow from numbering the characters.
| Text in A2 | Formula | Result |
|---|---|---|
| 123-ABC-456 | =MID(A2,5,3) |
ABC |
| Excel2024 | =MID(A2,6,4) |
2024 |
| PL-11-XYZ | =MID(A2,4,2) |
11 |
| Excel to potężne narzędzie | =MID(A2,7,2) |
to |
The first example selects letters between hyphens, while the second selects digits. MID returns text in both cases. An extracted year is not automatically a valid date, and a code consisting of digits is not necessarily a quantity that should be summed.
A moving position after a delimiter
For A2 containing ola@example.test, I want the part after @. The prefix can vary in length, making a fixed starting position fragile. I combine SEARCH with LEN.
=MID(A2,SEARCH("@",A2)+1,LEN(A2))
The @ character is fourth. Adding 1 starts extraction at the fifth character, e. I supply the full text length as a sufficiently large character count; MID stops at the end. The result is example.test, without padding the output with extra characters.
This extraction assumes one delimiter and prepared input. It is not complete email-address validation. A missing @ causes a search error, while additional occurrences require a decision about which one matters. I do not hide such records by automatically converting every error into an empty result.

The final characters and an off-by-one mistake
To select the last five characters from text at least five characters long, the start is its length minus five plus one. If A2 contains ABCDE12345, the length is 10, so extraction begins at position 6.
=MID(A2,LEN(A2)-5+1,5)
The result is 12345. Omitting the added one moves the start to 5 and produces E1234. Text shorter than five characters can give this particular formula an invalid starting position. If the requirement simply concerns the end of a string, RIGHT is often a clearer choice than extending a middle-extraction expression.
Initials have a similar assumption problem. A hard-coded position 5 might fit one first name and select a space for another. A general solution must define the separator and treatment of multi-part names. I do not present one convenient example as a universal rule for people’s names.
Empty text and errors are different results
For ABC, a starting position of 5 returns empty text, "". Starting at 2 and requesting 10 characters returns BC. A character count of 0 returns empty text. A start below 1 or a negative character count produces a VALUE error: #VALUE! in English and #ARG! in Polish Excel.
A start beyond the text’s end is therefore not an error. An empty result can be correct function behaviour while also indicating that the source failed the assumed format. When reviewing an import, I want both the extracted value and an explanation of why an expected fragment was absent.
The examples use ordinary letters and digits. Microsoft also documents differences in counting certain Unicode characters depending on workbook compatibility version. When a format allows those characters, I do not assume that one visible symbol always occupies one position. Our ASCII identifier does not introduce that additional complication.
When TEXTAFTER is clearer
If the task is “take everything after @”, Microsoft 365 or Excel 2024 offers TEXTAFTER: =TEXTAFTER(A2,"@"). This names the delimiter directly instead of combining several functions. I do not attribute that availability to perpetual Excel 2016 or 2019.
MID remains useful for fixed positions, a specified length, and older supported versions. The right choice depends on the data format and the recipient’s environment. A good formula fits those conditions rather than simply using the newest available function.
Exercise: change the format
For INV-2026-007, identify the starting position of 007 and return three characters. The expected start is 10. Then change the prefix to INVOICE and explain why the fixed position no longer fits. This is a useful point to consider a delimiter-based approach.
Add an example with no delimiter and decide how to mark it for correction. See SEARCH for locating a starting point. To assemble text from prepared components, continue with TEXTJOIN.
Key takeaways
Is ŚRODEK a valid Polish name for MID?
No. The correct Polish name for MID is FRAGMENT.TEKSTU.
What if the start is beyond the text?
MID returns empty text rather than an error. A start below 1 or a negative character count causes an error.
How do I extract 099 from ORD-2024-099?
Use a start of 10 and a length of 3. The ninth character is a hyphen, so starting at 9 produces -09.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
