Excel Left, Right, and Mid Functions

Excel is a great application for importing data. Sometimes, though, imported text does not appear just as you would like. Read on to learn how the LEFT, RIGHT and MID functions can help you resolve these issues.

// //

Isolating Parts of an Excel Cell

Excel’s LEFT, RIGHT and MID functions make it simple for you to slice up and use cell contents in other cells of a spreadsheet. For instance, perhaps you have imported a text file into Excel using .csv file format. (If you are unfamiliar with this process, check out the BrightHub tutorial How to Import CSV Files.) You can then use that text to create a database.

Reformatting Excel Data

If you have a large text file in an Excel column that you need broken up and used in other cells, the LEFT, RIGHT and MID functions will make light work of it. This is especially true if all of the text is the same length.

This is because the syntaxes for LEFT, RIGHT and MID are as follows:

LEFT(text,num_chars)

RIGHT(text,num_chars)

MID(text,start_num,num_chars)

What this means is that Excel will count the number of characters you specify to “cut” and use the text in the cells to which the function is applied.

For example, we have a text list with numbers in ten-digit phone number format. We want to break down the list into an area code column, a prefix column, and a suffix column.

To begin, we will select the first cell in the Area Code column. In the Formula Bar, we will enter =LEFT( and Excel will prompt us for the text and number of characters. We know we want to draw the text from cell B4, and we also know we only want to use the first three characters from the left side of that cell. Therefore, the formula we enter will be =LEFT(B4,3).

Next, we want to extract text from the first column and fill in the Prefix column. To begin, we will select the first cell in the Prefix column and go to the Formula Bar. Enter =MID( and Excel prompts us for the text, start number and number of characters we want to use. We know we want to extract the text from cell B4. We know the start number is “5” because we want to start with the fifth character (we don’t want to use the dash). Finally, we know that we want to extract three characters. Therefore, the formula for this cell will be =MID(B4,5,3).

Finally, we want to extract the suffix from the text column. Just as with the previous instances, we’ll select the first cell and enter =RIGHT( into the Formula Bar. Excel will prompt us for the text source and the number of characters we want. For this cell, the formula will be =RIGHT(B4,4).

Once we have the formulas entered into each of the initial cells, we can select those three, grab the fill handle, and copy the formulas all the way down.

Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data in Excel

Use Ctrl+Shift+Enter (CSE formulas) to supercharge your formulas in Excel! Yes, it is true…there is a secret class of formulas in Excel. If you know the magic three keys, you can get a single Excel array formula to replace thousands of other formulas.

95% of Excel users do not know about CSE Formulas. When most people hear their real name, they think “That doesn’t sound the least bit useful” and never bother to learn about them. If you think SumIf and CountIf are cool, you will soon discover that Ctrl+Shift+Enter (CSE) formulas will run circles around SumIf and CountIf. CSE formulas allow you to literally replace 1000’s of cells of formulas with a single formula. Yes, my fellow Excel Guru’s, there is something this powerful sitting right under our noses and we never use it.

Before there was SumIf, you could use a CSE formula to do the same thing as SumIf. Microsoft gave us SumIf and CountIf, but CSE formulas are not obsolete. Oh no, and they can do much more! What about if you want to do AverageIf? How about GrowthIf? AveDevIf? Even MultiplyByPiAndTakeTheSquareRootIf. Just about anything you can imagine can be done with one of these CSE formulas.

Here is why I think CSE formulas never caught on. First, their real name scares everybody away. Second, they require a foreign, counter-intuitive handling in order to make them work. After you type in a CSE formula, you cannot just hit Enter. You cannot just exit the cell with a click of an arrow key. Even if you get the formula right and hit the enter key, Excel gives you the totally non-user friendly “VALUE!” error. It doesn’t say, “Wow – that is beautiful. You are 99.1% of the way there,” which you probably were.

Here is the secret: After you type a CSE formula, you have to hold down the Ctrl and the Shift keys, and then hit Enter. Grab a sticky note and write that down: Ctrl Shift Enter. Power Excel users will have the opportunity to use these formulas about once a month. If you don’t write Ctrl Shift Enter down right now, you will forget it by the time something comes up again.

Examine this example: Say you wanted to average just the values in column C where column A was in the East region.

The formula in cell A13 is an example of a CSE formula.

=AVERAGE(IF(A2:A10=”East”,C2:C10))

Plug this in and you will get 7452.667, the average of just the East values. Cool? You just created your own version of the nonexistent Excel function AverageIf. Remember: Hit Ctrl+Shift+Enter to enter the formula.

Check out the next example below.

In this example, we make the CSE formula more general. Rather than specifying that we are looking for “East,” indicate you want whatever value is in A13. The formula is now =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).

Oddly enough, Excel will let you copy and paste CSE formulas without any special keystrokes. I copied C13 to C14:C15 and I now have averages for all of the regions.

Our mainframe system stores Quantity and Unit Price, but not the extended price. Sure, it is easy enough to add a column C and fill it with =A2*B2 and then total column C, but you don’t have to!

Here, our CSE formula is =SUM(A2:A10*B2:B10). It takes each cell in A2:A10, multiplies by the corresponding cell in B2:B10 and totals the result. Type the formula, Hold down Ctrl and Shift while you hit enter, and you have you answer in one formula instead of 10.

Do you see how powerful this is? Even if I had 10,000 rows of data, Excel will take this single formula, do the 10,000 multiplications and give me the result.

OK, here are the rules: You have to hit Ctrl+Shift+Enter anytime you enter or edit these formulas. Failure to do so results in the totally ambiguous #VALUE! error. If you have multiple ranges, they all have to have the same general shape. If you have a range mixed with single cells, the single cells will be “replicated” in memory to match the shape of your range.

After you successfully enter one of these and look at it in the formula bar, you should have curly braces around the formula. You never enter the curly braces yourself. Hitting Ctrl+Shift+Enter puts them there.

These formulas are tough to master. MrExcel gets a headache just thinking about them. If I have a tough one to enter, I go to Tools>Wizards>Conditional Sum wizard and walk through the dialog boxes. The output of the conditional sum wizard is a CSE formula, so this usually can give me enough hints on how to enter what I really need.

Did you have to take BASIC in 11th grade with Mr. Irwin? Or, worse yet, did you get a “D” in linear algebra with Mrs. Duchess in college? If so, you are in good company and will shudder whenever you hear the word “array.” – I run screaming in the other direction whenever anyone says array. I understand them, but this is Excel, I don’t need arrays here!. The evil secret is that Excel and Microsoft call these formulas “array formulas”. Stop — don’t run away. It is OK, really. MrExcel has renamed them CSE formulas because it sounds less scary, and because the name helps you remember how to enter them — Ctrl Shift Enter. I only mention the real name here in case you run into someone from Microsoft who never had Mrs. Duchess for linear algebra, or, in case you decide to peruse the Help files. If you go to help, search under the evil alias of “array formula,” but between us friends, let’s call them CSE — OK?