applied tips : february 2007

microsoft excel : breaking up is easy to do

In many situations, Excel spreadsheet data needs to be rearranged within cells to be useful. For example, maybe you have data items scrunched together in one cell and you need to separate parts of the data into different cells. What do you do?

Instead of spending hours retyping every piece of data into the proper cells, Excel has functions (predefined formulas) that can break the information into separate cells for you. Let's look at a scenario where you might want to use these functions.

Breaking up

Many companies have long customer numbers to identify each customer. Think about your electric bills and the long strings of mysterious ID numbers that identify you as a customer in the company's databases.

Pretend you work for Sparks Electric Co. and an example of a customer account number is 1234567890. The first three numbers, 123, relate to the type of service the customer receives (residential or commercial, for example). The middle digits, 456, identify the customer's location and 7890 personally identifies the customer.

As part of your job, you manage an Excel spreadsheet listing the company's customers, and the customer account numbers are entered as one cell, as shown below. .

Unfortunately, your boss wants a spreadsheet analysis showing the three segments of the account number (service type, location, and customer ID number) in separate cells.

There may be hundreds, or even thousands of rows of customers, and you don't want to retype each one into the proper arrangement. What do you do?

This issue of OfficeClues covers the LEFT, RIGHT, MID, and CONCATENATE functions. These functions will enable you to quickly slice your data into useful cell arrangements.

LEFT function

=LEFT(text,num_chars)

Example: =LEFT(A4, 3)

Extracts the first three digits of the value in cell A4 starting from the left.

The LEFT function allows you to extract characters from the left side of a value and put them in a new cell. To do this, click in an open cell?in the example we used cell B4?and click on the Function button on the toolbar. The Insert Function (or Paste Function if you use Excel 97 or 2000) dialog box opens, allowing you to search for and select a function. Select the LEFT function and click OK.

The Function Arguments dialog box opens. The cursor should be in the text box labeled Text. In your worksheet, click on the cell containing the value you would like to extract from, in this case cell A4 (you can also type in A4).

Click in the Num_chars text box and enter the number of characters you would like to extract. The first three numbers, 123, relate to the customer's service type, so type 3. Click OK. The first three numbers, 123, appear in cell B4.

MID function

=MID(text,start_num,num_chars)

Example: =MID(A4, 4, 3)

Extracts three digits from the middle of the value in cell A4.

The MID function is similar to the LEFT function, except that it extracts characters from the middle of a value. To use it, insert the MID function into cell C4 by clicking on the Function button in the toolbar to open the Insert Function dialog box. Select the MID function and click OK.

The Function Arguments dialog box opens. Click in the Text box and then click on cell A4 to select it, just like you did for the LEFT function.

Next you have to enter the position of the first character you want to extract (this is the one tricky part about using the MID function). In the Start_num text box, type 4 because you want to start your extraction starting at the fourth digit.

Finally, click in the Num_chars text box to enter the number of characters you want to extract, in this case 3. Click OK. The middle three numbers, 456, appear in cell C4.

RIGHT function

=RIGHT(text,num_chars)

Example: =RIGHT(A4, 4)

Extracts the last four digits of the value in cell A4 starting from the left.

The RIGHT function helps you extract characters from the right side of a value. Select cell D4, click on the Function button on the toolbar, and select the RIGHT function from the Insert Function dialog box.

The Function Arguments dialog box opens. The cursor should be in the Text box. In your worksheet, click on cell A4. Next click in the Num_chars text box. Type 4 because you want to extract the four numbers from the right side of the value in cell A4. Click OK.

Getting back together

You show your boss the spreadsheet with the account numbers separated into segments, and he is impressed.

An hour later, however, he tells you he wants the account numbers back together in single cells. Unfortunately, you deleted the column containing the combined account numbers and all you have are the three new segments in separate cells. How can you combine the data from the three cells back into one?

CONCATENATE function

=CONCATENATE(text1,text2,?)

Example: =CONCATENATE(B4,C4,D4)

Joins several values into one cell.

The CONCATENATE function takes the values from up to thirty cells and combines them into one value.

Click in an empty cell, in this case D4. Click on the Function button on the toolbar. The Insert Function dialog box opens. Select the CONCATENATE function. The Function Arguments dialog box opens. Click in the Text1 text box and click cell A4. For Text2 click cell B4, and for Text3 click cell C4. Click OK and Excel combines the values from all three cells and puts 1234567890 in cell D4.

Tip: These functions can also be used to manipulate text or single-cell references.

schedule a class on excel

Request an on-site Excel class from Applied Office. Sessions are just $95/hr and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time.  Learn more here

upcoming workshop

Sign up now for the Excel: Understanding Formulas workshop at the Lodi Chamber of Commerce, March 7th at noon.

Learn exactly how formulas work from the basics (simple arithmetic) to the complex (IF, financial formulas, etc). Time and date calculations, text manipulation, and handy formula tricks will also be covered. Watch how a time card is created that automatically calculates overtime, wage, and vacation accrual.  More Information

quick reference card

Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your own printer. You might even want to laminate it.