Product codes, order references and account numbers all bury something useful in the middle. The MID function is how you cut that piece out without touching the rest.
First, three functions share the job. LEFT takes characters from the start, RIGHT takes them from the end, and the one in between takes them from wherever you point it.
For instance, a code like AB-482-XL hides a batch number between two dashes. One formula lifts it into a column of its own, ready to sort or match against something else.
The MID function.
So give it a cell, a starting position and a length.
=MID(A2,4,3)
Counting starts at one rather than zero, so character four is the fourth thing you can see. In short, the MID function reads exactly as it looks, which is rarer than it sounds.
Also, the siblings are one line each. =LEFT(A2,2) returns AB and =RIGHT(A2,2) returns XL, and both count from their own end of the string.
It never tells you it ran out.
Here is the part that costs an afternoon. Ask for more characters than the cell contains and you get what was there, silently, with no error of any kind.
=MID(A2,FIND("-",A2)+1,3)Consequently a hard-coded position only works while the data stays the same width. The moment a shorter code arrives, the column fills with answers that look like answers and are two characters short.
Therefore the durable version stops guessing. FIND locates the dash and hands its position over, so the formula measures the string instead of assuming it.
Result of the MID function.
Before. Three codes of different lengths, with nothing extracted yet.
After. Two clean cuts, and one row that quietly returned a single character.
A2 -> AB-482-XL A3 -> CD-917-M A4 -> EF-7 =MID(A2,4,3) -> 482 =MID(A3,4,3) -> 917 =MID(A4,4,3) -> 7 (three asked for, one returned) =LEFT(A2,2) -> AB =RIGHT(A2,2) -> XL
That third row is the whole warning. Nothing on screen separates a correct answer from a truncated one, so the error surfaces later, in whatever you matched the column against.
Two functions rescue it. FIND gives you a position that moves with the data, while LEN tells you whether there was enough string to begin with.