Sunday, May 18, 2014

MID function in MS-Excel

C
D
E
F
3
Text
Start
Position
How Many
Characters
Mid String
4
ABCDEDF
1
3
ABC
 =MID(C4,D4,E4)
5
ABCDEDF
2
3
BCD
 =MID(C5,D5,E5)
6
ABCDEDF
5
2
ED
 =MID(C6,D6,E6)
8
ABC-100-DEF
100
 =MID(C8,5,3)
9
ABC-200-DEF
200
 =MID(C9,5,3)
10
ABC-300-DEF
300
 =MID(C10,5,3)
12
Item Size: Large
Large
 =MID(C12,12,99)
13
Item Size: Medium
Medium
 =MID(C13,12,99)
14
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.
C
D
34
Branch ID
Postal Region
35
DRS-CF-476
CF
 =MID(C35,5,2)
36
DRS-WA-842
WA
 =MID(C36,5,2)
37
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.
C
D
45
Full Branch Code
Postal Region
46
DRS/STC/872
STC
47
HDRS/FC/111
FC
48
S/NORTH/874
NORTH
49
HQ/K/875
K
50
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 /

No comments:

Post a Comment