MS Excel 2003 Training 101 - Count, Combine and Compare

Function name
LEN - count the number of characters in a cell
CONCATENATE - combine information from two cells into one cell
EXACT - compare two cells

Function names summarize long formulas.

Instead of counting the number of letters in a cell, retyping to combine information from two cells into one, or proofreading to compare cell contents, let Excel do the work for you by using formulas. In the practice session, you'll have a chance to use the formulas and functions you've learned about.

Count characters in a cell

 Formula in the worksheet.
  Formula result in the worksheet.

Say that you've written a list of headlines to be posted on your Web site, and they cannot be over 40 characters (letters, numbers, and spaces) long. To ensure that the character count is within that limit, you would type this formula in cell B1:

=LEN(A1)

The LEN function finds the number of characters in cell A1 and reports the result, which is 35.

The next step would be to copy the formula in cell B1 down the column to find the number of characters in the other cells. You'll have a chance to try this in the practice session at the end of the lesson.

Combine text from two cells into one cell

 Formula in the worksheet.
  Formula result in the worksheet

In this example, a text file has been imported into Excel. First names and last names are in two separate columns. To combine the information into one cell, you would use a formula that is unlike what you've seen before. It uses the ampersand (&) operator instead of a function. You would type this formula in cell C2:

LEN count the number of characters in a cell

The ampersand tells Excel to combine the text from cell A2, and a space (identified by the quotation marks around it), and the text from cell B2.

To combine the other names into other cells in column C, you would copy the formula down the column.

Note    You can do the same operation by using the CONCATENATE function: =CONCATENATE(A2," ",B2), but it's faster to just type the ampersand. "Concatenate" means to combine, by the way, or had you guessed?


Compare cells

 Formula in the worksheet.
  Formula result in the worksheet.

Imagine that the names in a master worksheet have been copied into another worksheet. Time has passed, changes may have occurred, and you want to compare the two groups of names to find any differences.

You would copy the second group of names into the master worksheet, and then you would type this formula in cell C2:

=EXACT(A2,B2)

The EXACT function compares the text in two cells. It displays TRUE if the cell contents are exactly the same, or FALSE if they are not. The EXACT function is case-sensitive (that is, it sees capital letters as different from small letters), but it ignores formatting differences such as bold or italic.

The formula result is FALSE, because "Davolio" is spelled differently in cells A2 and B2.

To compare the rest of the names, you would copy the formula down the column.


Separate text

Function name
LEFT
RIGHT
FIND
LEN

Function names express long formulas quickly.
In this lesson, you'll look at a cell that contains an order ID, an item description, and a price. You'll learn how formulas can pull the order ID into one cell and the price into another cell, leaving the original information intact. Looking at a cell that contains two names, you'll learn how you can use formulas to separate the first name or the last name into another cell.


Use LEFT to separate the order ID


 Formula in the worksheet.
Formula result in the worksheet.

With three different pieces of information in each cell, column A is not really convenient to use. To separate the order IDs into a column containing just the order IDs, you would type this formula in cell B2:

=LEFT(A2,5)

The LEFT function finds and displays the first characters in a cell, up to a number that you specify. In this example, the order IDs contain 5 characters, so LEFT looks in cell A2 for the first five characters from the left, which it then displays in cell B2.

For the complete group of order IDs, you would copy the formula down the column. Then, if you had no further need for the copied formulas, you could delete them, leaving the results in the column. (You learned how to do that in the practice session for the previous lesson.)


Use RIGHT to separate the price

 Formula in the worksheet.
Formula result in the worksheet.


To create a group containing just the price you would type this formula into cell B2:

=RIGHT(A2,6)

The RIGHT function finds and displays the last characters in a cell, up to a number that you specify. In this example, the price always contains six characters (including the dollar sign), so RIGHT looks in cell A2 for the last six characters on the right, which it then displays in cell B2.

For the complete group, you would copy the formula down the column. Then you could delete the formulas to leave only the results.

Use FIND to separate the first name

 Formula as typed.
  Formula in the worksheet.
  Formula result in the worksheet.


Imagine that you are preparing a mailing list by separating first and last names from one cell into cells of their own, so that either the first name or the last name can be used in a form letter's salutation.

Unlike order ID numbers, the lengths of the first names vary, so the formula using a specified number of characters wouldn't work here. To separate a first name, you would type this formula in cell B2:

=LEFT(A2,FIND(" ",A2)-1)

The space between the quotation marks tells the FIND function to look for the space in cell A2. (The -1 argument deletes the space itself from the result.) Then the LEFT function uses the result to display the text to the left of that space. That displayed text is the first name.

For the complete group of names, you would copy the formula down the column and then delete the formulas leaving only the results.


Use RIGHT to separate the last name

 Formula as typed.
  Formula in the worksheet.
  Formula result in the worksheet.

When the lengths of the last names vary, how does Excel recognize a last name? By the fact that it follows a space. To separate the last name, you would type this formula in cell B2:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

The formula uses the LEN function to find the length of the text in cell A2: 13 characters. Then the FIND function locates the space (" ") in cell A2. Next, Excel subtracts the position of the space (6 characters from the left) from the length of the text in cell A2. The RIGHT function uses the result of that subtraction as a character count to display everything to the right of the space. So 13-6 equals "Davolio" (the last 7 characters in the cell).

To complete the group of names, you would copy the formula down the column and then delete the formulas, leaving only the results.