LEFT, MID, & RIGHT

Have you ever had the need for data that's in a cell, but not the entire cell? When the need arises to dissect a cell, Excel has you covered.

Whether the data you need is in the beginning of a cell, the middle of a cell, or the end of a cell - there's a formula for that.

Where I work, we use a lot of dates in the format of YYYYMMDD (today is 20110430). Let's say you want to separate those into separate cells so you can subtotal them by their particular month. You need to figure out a way to split the cell so you can have the data you need. Because all of the data in this example is 8 characters long, there are a couple of different ways you can accomplish this; but today we're going to focus on using the LEFT, MID, & RIGHT formulas. Let's get right to it.

LEFT

In order to extract the year from from the data, you use the LEFT formula. Before using this formula, you need to know two things; the cell that contains the data and the length of the data you'd like to extract. The formula takes the form of =LEFT(Cell that contains the data, the number of characters you'd like to extract). Here's a visual example:

And the result:

MID

To extract the month it's a little trickier (because it's in the MIDdle of the data), but it's still just as easy. This time you need to know THREE things before you can use the formula; the cell that contains the data, the position of the first character you want to extract, and the length of the data you'd like to extract. The formula takes the form of =MID(Cell that contains the data, the position of the first character you want to extract, the number of characters you'd like to extract). Here's a visual example:

And the result:

RIGHT

Using the RIGHT formula is just like using the LEFT formula except you're starting from the right instead of the left (it it weren't obvious enough!) Therefore, you need to know the same two things; the cell that contains the data and the length of the data you'd like to extract. The formula takes the form of =RIGHT(Cell that contains the data, the number of characters you'd like to extract). Here's a visual example:

And the result:

Here is the final result:

I hope I've explained it easy enough! At a later date, I'll explain the other method we could have used to accomplish this task - Text to Columns. As I stated earlier, only because every cell has the same amount of characters AND they are all in the same position (the first four positions are the year, the next two are the month, and the last two are the day) could we use this different method.


Until next time... Keep Excelling!!!