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:


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


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:


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.


Leave a Reply