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.

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:

110308/20/7R13504

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:

=left(a2,6)

This formula will return this result:
110308

The RIGHT function works the same way:
=right(a2,7)

Will return this result:
7R13504

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!

Concatenate

Concatenate is a big word and a handy function!

It means to link together like a chain. And, that is exactly what you will do with this handy formula.

Click on your formula bar.


Type in the formula. You can combine your own text, like I did with the "/" and you can use values in your spreadsheet, like I did with the A2, B2, and C2.

Neat-o!

A few hints: if you use text and you need spaces, include those spaces between the quotation marks.

If you are building a date using concatenate, although it will look like a date, Excel thinks it's just a text field. Using format cells to change the format to a date still won't make Excel think it's a date. You'll have to change the formula to look like this:
=DATEVALUE(CONCATENATE(A2,"/",B2,"/",C2))
And, then change the format to make it look like a date. Now concatenate is a handy function, but Datevalue is even better. It's a guru function. Keep that one in your back pocket.