Function guides · Text
MID function
Returns characters from the middle of text, starting at a position you choose.
Syntax
=MID(text, start_num, num_chars)
| Argument | What it is |
|---|---|
text | The text or cell to take characters from. |
start_num | Position of the first character to take. The first character of the text is 1. |
num_chars | How many characters to return. |
What MID does
MID returns a stretch of text from the inside of a cell. You tell it where to start counting and how many characters to take.
LEFT and RIGHT work from the ends. MID is for the part in between, such as the digits inside a part number, the area code in a phone number, or the domain in an email address.
Positions count from 1. If the position of the start is not fixed, FIND or SEARCH can supply it. The result is always text.
A good mental model is to count characters on the page. In “HRD-0932-B”, H is 1, R is 2, D is 3, the dash is 4 and the 0 is 5. Asking for MID(A2,5,4) takes “0932”. When the position comes from FIND, the arithmetic is the same: FIND returns where the dash is, and adding 1 moves to the first character after it.
If you have a long column to split once, Data > Text to Columns with a fixed width or a delimiter is faster than any formula. MID is the better choice when the source data keeps changing and the split must update.
MID examples
1. Area code from a phone number
| A | B | |
|---|---|---|
| 1 | Phone | Area |
| 2 | (808) 555-0114 | 808 |
In "(808) 555-0114" the area code starts at character 2 and is 3 characters long. MID takes it from the middle, which LEFT and RIGHT cannot do.
2. The section between two dashes
| A | B | |
|---|---|---|
| 1 | ID | Number |
| 2 | HRD-0932-B | 0932 |
The ID HRD-0932-B has a numeric part in the middle. FIND locates the first dash, and MID starts one character later and takes four.
3. Everything after a character
| A | B | |
|---|---|---|
| 1 | Domain | |
| 2 | kai@oahuco.com | oahuco.com |
When the length of the tail is unknown, ask for more characters than could exist. MID stops at the end of the text. Here it takes everything after the at sign.
Common MID mistakes
- Starting at 0. Positions start at 1. A start of 0 returns #VALUE!.
- Off-by-one errors with FIND. FIND returns the position of the character itself. Add 1 to start after it.
- Numbers that lose leading zeros. MID returns text, so "0932" stays as written. If you convert it with VALUE, the zero goes.
- Splitting text with a variable pattern. If the structure varies, fixed positions fail. Use FIND for the delimiters or Text to Columns for a one-time split.
MID on Excel job tests
To "extract the three-digit product code from the middle of the SKU" or "get the month from a date written as text", use MID. Look at the longest and shortest values in the column first, because one longer field breaks a fixed-position formula.
Choose the shortest tool that works. Start of the text: LEFT. End: RIGHT. Fixed middle position: MID with literal numbers. Between delimiters: MID with FIND.
If you have to split a long column once and the data will not change, Data > Text to Columns is faster than any formula.
Related functions
MID questions
- What is the difference between MID and LEFT?
- LEFT always starts at the first character. MID starts at the position you give.
- How do I get everything after a certain character?
- Use FIND for the position and a large length:
=MID(A2,FIND("@",A2)+1,100). - Does MID return a number?
- No, it returns text. Wrap in VALUE for a number.
- What if start_num is longer than the text?
- MID returns an empty string.
Practice MID
Try it in a working grid: the free lesson LEFT, RIGHT and MID: pull out part of a cell (module 9 of Everyday Excel) has checked MID exercises.
Practice this function