MID
Cuts a piece out of the middle of text: you say which character to start from and how many to take. Good for codes where the part you need is inside.
MID(text, start_num, num_chars)- text
- Where to cut from, for example cell A2.
- start_num
- Which character to start from. The first character is 1.
- num_chars
- How many characters to take starting from that position.
Return value
Text: the given number of characters starting from the given position. If the position is past the end of the text, the function returns empty text.
Examples
Year from an order number
fx
A
B
C
1
2
3
4
5
6
7
Order number
Year
OR-2024-0153
OR-2023-1187
OR-2025-0042
OR-2024-0930
Sheet1
Details
MID takes the text, the number of the character to start from, and the number of characters. Characters are numbered from 1, and dashes and spaces count too.
If you ask for more characters than are left until the end, MID returns the rest without an error.
When the part you need is in different places, find the start with FIND. For example, FIND("-",A2)+1 points to the character right after the dash.
Common mistakes
- The second and third arguments are swapped: first the starting character, then how many.
- The position is off by one. In "OR-2024" the year starts at the 4th character, because the dash counts too.
- The result is text. To compare the year with the number 2024, wrap MID in VALUE.