IQ Accounting Solutions

Have you ever wanted to create a new column on a spreadsheet with part of the information already in another column? For example, you might have a column of zip codes with 4 digit extensions and you want to put the 5 digit zip code in column by itself and the 4 digit extension in a separate column. One easy way to solve this problem is by using the Left, Right, or Mid functions.

• LEFT lets you specify the number of characters you want from the beginning of another cell or from a specified string of text or numbers
• RIGHT lets you specify the number of characters you want from the end of another cell or from a specified string of text or numbers
• MID lets you specify the number of characters you want from a point you specify in the middle of another cell or specified string of text or numbers

Here is a sample spreadsheet with the Zip+4 in column A.
Column B shows the formula using the LEFT function to get the first 5 digits of the Zip+4 from column A.
Column C shows the formula using the RIGHT function to get the last 4 digits of the Zip+4 from column A.

Here is what the formulas mean. LEFT or RIGHT tell Excel if you want to take characters from the left (beginning) or right (end) of the the specified cell. A2, A3 and A4 tell Excel which cell you want to retrieve information from. After the cell location is a comma followed by the number of characters you want from that cell. In this case 5 in column B or 4 in column C. If you have trouble remembering how to type the formula you can use the function wizard. After you enter your formulas once, you can copy them down each column and Excel will adjust the row numbers for you.

Here is what the finished spreadsheet will look like.

MID lets you pull characters from the middle of a cell or string of text. That may not seem useful here, but what if your original column of zip codes isn’t as consistent as it was in the last example. What if it looks like this:

If we used RIGHT on this spreadsheet, most of data in the +4 column would be wrong. But the extra flexibility of MID works to your advantage.

The formula in C2 of this example says “Look in cell A2 and, starting with the 7th character, give me 4 characters.” In this case the result will look like this: