Wednesday, November 5, 2008


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:

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:

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:

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

Tuesday, November 4, 2008

LEFT, RIGHT...Just Vote!

Let's say you've run a report and downloaded it to Excel. Let's also say that this report doesn't have a need by date field, but the need date is part of a reference code that looks like this:


Let's also say that you want to do a pivot table where all your records are summarized by need by date and you are OK with the format mmddyy (for now).

Here's what you do:


This formula will return this result:

The RIGHT function works the same way:

Will return this result:

These are definitely nice functions to have in your toolbox. And, there is more. I'll look at MID tomorrow.

Oh, and if you're in the US and you're on the left or on the right, I don't care. VOTE TODAY!

Monday, November 3, 2008


I am going to try and keep this short and simple and to the point. If you knew me at all, you'd know that's a challenge, but I'm really going to try!

OR is a function like AND and ISERROR in that it returns a TRUE or FALSE result. So, like AND and ISERROR, OR is pretty worthless unless you couple it with an IF. It's like a Peanut Butter and Jelly sandwich without the bread.

If you create a formula like this:
You will either get a TRUE or a FALSE result.

If that result is TRUE, you can use an IF statement to have it return a specific value, a result of a formula, or the value in a nearby cell. Or, 60 other things you might come up with. Here's an example where if at least one argument in my OR statement is TRUE, the formula returns the value found in H2. Otherwise, it returns a value of blank.


Regarding syntax, please note that your arguments in the OR statement are simply separated by commas. I often try to sneak one of these () in there, but that's just silly. Commas only. (The same is true for AND).

Finally, your OR statement is limited to only 30 arguments. Just 30. 31 is 1 too many, OK? (The same is true for AND).

Just in case I didn't make it clear enough, and because I think I've made my posts on AND and ISERROR too long and complex: OR, AND, ISERROR are all similar in that they all return TRUE or FALSE results and don't make much sense if you don't use them in conjunction with IF.