MS Excel: MID() Function
Microsoft’s Excel has a function called MID(), this function can be used to find the location of a particular character or string within a string. But unlike the LEFT() & RIGHT() functions you can designate where to start.
Understanding the way Excel uses MID()
This formula required 3 different inputs; HAYSTACK, START POSITION and a CHARACTER TALLY.
The formula is as follows:
MID(HAYSTACK, START POSITION, CHARACTER TALLY)
Using MID()
If I had the string “Ian Blott, Web Developer, Sydney Australia” in cell A1 and I wanted to use the substring that is 20 characters long and starting at character 10 i would use MID()
MID(A1, 10, 20)
This would return the value “, Web Developer, Syd”
Getting Fancy
NOTE: This one is going to require that you understand the SEARCH() function
If I had a list of employee’s in a multinational company and they where all listed as single strings “Name, Position, Location” and I wanted to get just the list of all the positions held, I can use the following Formula
MID(A1,SEARCH(",",A1,1)+1,SEARCH(",",A1,SEARCH(",",A1,1)+1)-SEARCH(",",A1,1))
Breaking this down:
Assign A1 as the HAYSTACK for MID()
MID(A1,
Assign the position AFTER the first comma in A1 to the START POSITION
SEARCH(",",A1,1)+1
Calculate the position of the last comma in A1, have that be subtracted by the position of the first comma and I get my CHARACTER TALLY
SEARCH(",",A1,SEARCH(",",A1,1)+1)-SEARCH(",",A1,1)
This actually returns ” Web Developer” – Notice the preceeding space, I can remove that by adding a TRIM() function around the MID() IE:
TRIM(MID(.....))
Side note:
As with all my posts, quite often they are going somewhere and require 2,3 or even 4 different functions to perform the final task. Bellow are the related posts that tell an overall story.