Wednesday, November 5, 2008

Mid

MID is very similar to LEFT and RIGHT in that it strips specified characters out of a string of text or numbers.

So, let's use the example we used yesterday:
110308/20/7R13504

Here, we know that the 20 in the middle represents a useful piece of information, like warehouse or factory or the number of pieces you need to produce. But, we also know that it's not useful stuck in that long string.

It is pretty simple to extract:
=MID(A2,8,2)

The formula says to go over 8 characters (including spaces) and return two characters starting with the 8th character.

The result of this formula is:
20

This is pretty easy stuff once you know about it. Definitely another good tool to have in your guru belt.

6 comments:

normalboy said...

Isn't this MID function a generalization of the LEFT and RIGHT functions? Just use MID(A2,1,8) to get the same result as LEFT(A2,8)? I guess using MID for RIGHT might be more annoying if it's a long string of characters, but using MID(A2,11,7) isn't too bad, right?

Anyway, just a thought. Pretty useful functions! =)

Chaitanya Sagar, Excel Expert said...

I use MID, RIGHT and LEFT in combination with Find.

For example, if I want to separate first name and last name, on a list of names, I use LEFT (1,{find space}) to get the first name and Right({find space}, {max number}) to find the last name.

Many times, I use MID to extract dates out of a text field like "20081201 12:54 AM" and reconstruct date. E.g. DATE (use mid for YEAR, use MID for MONTH, use MID for DAY).

I would love to know if you have other uses of mid.

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
英文發音真難 said...
This comment has been removed by a blog administrator.