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)
[/cc]

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)
[/cc]

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))
[/cc]

Breaking this down:

Assign A1 as the HAYSTACK for MID()

MID(A1,
[/cc]

Assign the position AFTER the first comma in A1 to the START POSITION

SEARCH(",",A1,1)+1
[/cc]

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)
[/cc]

This actually returns ” Web Developer” – Notice the preceeding space, I can remove that by adding a TRIM() function around the MID() IE:

TRIM(MID(.....))
[/cc]

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