MID Function Dictionary
Function Dictionary | Function Examples | Function Categories |
MID | ||||||||
Text | Start Position |
How Many Characters |
Mid String | |||||
ABCDEDF | 1 | 3 | ABC | =MID(C4,D4,E4) | ||||
ABCDEDF | 2 | 3 | BCD | =MID(C5,D5,E5) | ||||
ABCDEDF | 5 | 2 | ED | =MID(C6,D6,E6) | ||||
ABC-100-DEF | 100 | =MID(C8,5,3) | ||||||
ABC-200-DEF | 200 | =MID(C9,5,3) | ||||||
ABC-300-DEF | 300 | =MID(C10,5,3) | ||||||
Item Size: Large | Large | =MID(C12,12,99) | ||||||
Item Size: Medium | Medium | =MID(C13,12,99) | ||||||
Item Size: Small | Small | =MID(C14,12,99) | ||||||
What Does It Do ? | ||||||||
This function picks out a piece of text from the middle of a text entry. | ||||||||
The function needs to know at what point it should start, and how many characters to pick. | ||||||||
If the number of characters to pick exceeds what is available, only the available characters | ||||||||
will be picked. | ||||||||
Syntax | ||||||||
=MID(OriginalText,PositionToStartPicking,NumberOfCharactersToPick) | ||||||||
Formatting | ||||||||
No special formatting is needed. | ||||||||
Example 1 | ||||||||
The following table uses the =MID() function to extract a post code from a branch ID used | ||||||||
by a company. | ||||||||
It is assumed that all branch ID's follow the same format with the letters identifying the | ||||||||
postal region being in the 5th and 6th positions. | ||||||||
Branch ID | Postal Region | |||||||
DRS-CF-476 | CF | =MID(C35,5,2) | ||||||
DRS-WA-842 | WA | =MID(C36,5,2) | ||||||
HLT-NP-190 | NP | =MID(C37,5,2) | ||||||
Example 2 | ||||||||
This example shows how to extract an item which is of variable length, which is inside | ||||||||
a piece of text which has no standard format, other than the required text is always | ||||||||
between two slash / symbols. | ||||||||
Full Branch Code | Postal Region | |||||||
DRS/STC/872 | STC | |||||||
HDRS/FC/111 | FC | |||||||
S/NORTH/874 | NORTH | |||||||
HQ/K/875 | K | |||||||
SPECIAL/UK & FR/876 | UK & FR | |||||||
=MID(C50,FIND("/",C50)+1,FIND("/",C50,FIND("/",C50)+1)-FIND("/",C50)-1) | ||||||||
Find the first /, plus 1 for the Start of the code. | ||||||||
Find the second /, occurring after the first / | ||||||||
Calculate the length of the text to extract, by subtracting the position | ||||||||
of the first / from the position of the second / | ||||||||