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) [/cc]
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) [/cc]
This would return the value “, Web Developer, Syd”
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
Breaking this down:
Assign A1 as the HAYSTACK for MID()
Assign the position AFTER the first comma in A1 to the START POSITION
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
This actually returns ” Web Developer” – Notice the preceeding space, I can remove that by adding a TRIM() function around the MID() IE:
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.