MID
Returns a set number of characters from the middle of a text value, starting at a position.
MID(<text>, <start_num>, <num_chars>)Returns: Text (a scalar).
What it does
MID takes a slice out of the middle of a text value. You give it a starting position and a count, and it returns that part. It is used to pull a code out of a structured reference, such as the cost center inside a journal reference "JE-2026-CC042-0017". The first character is position 1.
Syntax and arguments
- text: the value to read from.
- start_num: the position of the first character to take. The first character is 1.
- num_chars: how many characters to take from that position.
Example
A journal reference holds the cost center in a fixed place, for example "JE-2026-CC042-0017", where "CC042" starts at position 9. The cost center report needs only that code.
Cost Center = MID(Journal[Reference], 9, 5)
MID starts at position 9 and takes five characters, which gives "CC042". Each journal line can then be grouped and totaled by cost center.
Example: a second, different use
Pull the year out of a date stored as text in the format "20260315":
Year Text = MID(Imports[PostingDate], 1, 4)
The result is "2026". Use VALUE to turn it into a number when the year must be filtered or sorted as a number.
Things to watch
- Positions are counted from 1, not 0. A start of 0 gives an error or an unexpected result.
- MID is only as reliable as the format. A reference with a different length moves the slice, so test a sample of the data.
- Use SEARCH or FIND to locate the start when the position changes from row to row.
- If the start is beyond the end of the text, the result is empty text.
- MID returns text. Convert it to a number before you sum it.
In business terms
MID is how a finance team extracts structured codes from references, such as cost centers, project codes, vendor sub-codes and document types. A clean extraction makes the reconciliation and the department summary match the ledger without retyping anything.