Excel track · Exercise 51 of 86 Paid
MID: characters from the middle
Read a fixed slice from the middle of a code, from a position you worked out.
Intermediate
Product codes that put two letters, a hyphen and four digits
Read-only data · screen 1 of 2 · answer in C2
In C2, read the four digits at the end of the code in B2 with MID.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Code | Answer |
| 2 | pen | AN-1042 | |
| 3 | pad | BO-2299 | |
| 4 | ink | CY-3140 |
In C2, read the two letters at the start of the code in B2 with MID.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Code | Answer |
| 2 | pen | AN-1042 | |
| 3 | pad | BO-2299 | |
| 4 | ink | CY-3140 |
Reference answer
What it does
MID reads a slice of text from a position you give it, and it always counts from the left. The code in B2 is AN-1042: two letters, a hyphen, then four digits. Position 1 is A, position 3 is the hyphen, and the digits begin at 4.
The formula =MID(B2,4,4) returns 1042 in C2: start at 4, read four characters. The start number is the one people mistype, because it is counted from the left even when the slice sits on the right.
Length is the argument that gets less attention. Four is exact here; a smaller number truncates the answer, and a larger one is harmless because MID stops at the end of the text. The second screen keeps the call shape and changes only the start.
Syntax
=MID(text,start,num_chars) counts positions from 1 and reads num_chars charactersWorked example
On Product codes that put two letters, a hyphen and four digits, =MID(B2,4,4) returns 1042.
=MID(B2,4,4)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The start position or the length is not a number, or the start is below 1. |
#NAME? | The function is spelled MD or MIDD, and Excel returns #NAME?. |
Wrong slice | A start of 3 keeps the hyphen and returns -1042. |
At work
Pulling a fixed part out of a product, order or account code.