MID Function (Functions in Excel)
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 / |
0 comments:
Post a Comment