The MID function in Excel
MID extracts part of a text starting at any position: "4 characters from position 4". It's how you pull a number out of the middle of a product code or the domain out of an e-mail address.
Syntax
=MID(text, start_num, num_chars)
| Argument | What it means |
|---|---|
| text | The text or cell to take characters from. |
| start_num | The position of the first character to take (1 = the first character). |
| num_chars | How many characters to take. Asking for more than are left simply returns the rest. |
Examples
1. A fixed position
D2fx=MID(A2,4,4)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Model | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | 4417 |
Characters 4 to 7 of SH-4417-BLU.
2. Start after a character
D2fx=MID(B2,FIND("@",B2)+1,100)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Domain | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | shopmail.com |
FIND locates the @ (position 12); MID starts one after it. 100 is simply "enough to reach the end".
3. A single character
D2fx=MID(A2,9,1)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Size code | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | B |
Length 1 returns just the character at that position.
Common mistakes
Counting from 0. Positions in Excel start at 1.
Fix: The first character is start_num 1.
Hard-coding positions on text of varying length. It breaks as soon as one entry is longer.
Fix: Find the position with FIND, or use TEXTAFTER in Excel 365.
Frequently asked questions
Does MID return a number or text?
Always text, even if it looks like a number. Use VALUE to turn it into a number.
Related functions
- LEFTLEFT returns the first characters of a text, RIGHT the last ones.
- FINDFIND and SEARCH return the position of one text inside another.
- LENLEN returns the number of characters in a text, spaces included.
- TEXTAFTERTEXTAFTER and TEXTBEFORE split text at a delimiter, such as the @ in an e-mail or a dash in a code, without counting positions.