A reader posed an interesting conversion problem between an old database and a new online one. She wanted to use an Excel file to move the data from one system to another. However, the new system would not accept field values longer than a certain number of characters. In this tutorial, I’ll show how to check the character count in Excel using the LEN function.

## Using the LEN Function

In another tutorial, we used **LEN **in conjunction with **TRIM **and the **SUBSTITUTE **function to count the number of words in a cell. But, in this case, we need to spot cell items that are too long. Fortunately, the reader had already split the first and last names so we didn’t have to deal with multiple columns.

The **LEN** function returns the number of characters for a given cell. This also counts spaces. You could then use Excel’s conditional formatting to color-code entries that exceed the field length specified in the record layout. Of course, you could also sort on the length column as well.

Instead of showing the company’s information, I’ll use some test names and addresses.

## Find Character Count with LEN,

- Open your Excel file.
**Insert**a column to the right of the field you wish to count.- Enter a descriptive label for the column. For example, I used
**Dcount**since I want to count the characters. - Place your cursor in the first cell under that column label. In my example, it’s D2.
- From the
**Formulas**tab, click**Insert Function…** - In the
**Insert Function**dialog, select the category of**All.** - Select the
**LEN**function from the list. (*Tip: you can press L to advance the list.*)

- Click
**OK**. The**Functions Arguments**dialog should appear. - Click in the spreadsheet cell you wish to count characters. The value should carry over to the
**Function Arguments dialog**. In my example, it would be C2. You can also see the Excel formula result in the red outlined area below.

- Click
**OK**.

Excel should show a number in that **Dcount** cell, which is your character count. Most likely, you will want to copy this formula to other cells in the same column.

### To copy the LEN formula to other cells in the column.

- Place your cursor in the first cell with the LEN formula.
- Scroll down with your mouse and
`Shift`+**click**in the last cell in your column - Press
`Ctrl`+`d`.

At this point, I know the character count for the **Address1** field. After that, I could either use Excel’s conditional formatting to color-code entries above a certain value or sort using **Dcount **column values. The last step is to fix the records that are too large and delete your **Dcount **column if you’re importing to another system.

### Look Out for Extra Spaces

As I mentioned above, this function also counts spaces. Often we tend to think of normal spaces between words. However, the LEN function will also count leading and trailing spaces. While it’s easy to see leading spaces because they stand out, it can be tricky with trailing spaces. Trailing spaces are blank characters added to the end of your entry that you can’t see.

If you look at the screen snap below, you’ll see that the address for **B3** looks longer than **C2**, and yet the **Dcount **values are quite different. That’s because I added a bunch of trailing spaces at the end. Granted, I added many spaces to make a point, but it’s easy for a single space to get added. Don’t worry. Excel has a **TRIM **function to fix this.

## Troubleshooting

There are many reasons for Excel formula errors, but there are other issues as to why the LEN function doesn’t work.

- The cell with your
**LEN**formula is formatted as text. If so, try changing it to general or number. You will probably have to re-enter the formula. - You may need to turn on Automatic calculations.

We often think of Microsoft Excel just as a number-crunching program. However, with the program’s built-in functions, such as **LEN**, you can use it to solve some interesting problems aside from counting characters in a cell. For example, it can be a great intermediary tool for spotting problem records or converting data. And once you get familiar, you can skip using Insert Function and type directly into the formula bar.