Alym LabsExcel for Accounting
← All functionsTaught in Unit 10 · The LEFT, RIGHT and MID functions
Text function

MID

Grab part of the text.

Purpose

Pull a run of characters out of the middle of a value

Returns

The characters you asked for, as text

Syntax

=MID(text, start, how_many)

ArgumentRequiredWhat goes here
textRequiredThe cell you are taking characters from, usually A2.
startRequiredWhich character to start at, counting from 1.
how_manyRequiredHow many characters to take from there, not the value you want.

How to use MID

MID takes three things: the value, the position to start at counting from 1, and how many characters to take from there.

The third argument is a count, not an end position. This is the mistake worth guarding against: to take characters 5 to 8 you ask for 4 characters starting at 5, not for 8.

Hard-coded positions only hold while every value is laid out the same way. Pairing MID with FIND finds the marker instead of assuming where it sits, and survives a reference that changes length.

Examples

Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.

Supplier import — raw.xlsxSheet1
A supplier export before anyone has cleaned it: padded names, a code buried in a reference, and amounts that arrived as text
ABCD
1ReferenceSupplier nameAmountPosted
2GL-6100-8801 brightwell paper 1500Y
3GL-6200-8802calder UTILITIES320y
4GL-6100-8803meridian freight ltd2750
5GL-6300-8804 ashgrove catering184Y
6GL-6200-8805THORNBURY print760
7GL-6100-8806Brightwell Paper1120Y
FormulaResult
=MID(A2,4,4)The account code, taken by position. Fine while every reference is GL-nnnn-nnnn.6100
=MID(A2,FIND("-",A2)+1,4)The same four characters, found rather than assumed.6100
=MID(A2,9,4)The invoice number at the end. RIGHT would be the shorter way to say this.8801
=VALUE(MID(A2,4,4))MID always gives text back, even when every character is a digit. VALUE turns it into a number you can total.6100

Notes

  • A start position past the end of the value gives an empty result rather than an error.
  • MID returns text. A code pulled out with it will not match a numeric account column until VALUE has been round it.
FINDWhere is it?LEFTTake characters from the start.RIGHTTake characters from the end.LENCount the characters.VALUETurn a number that arrived as text into a real number.
Practise MID in Unit 10

A reference tells you what it does. A unit makes you use it.