How to Count Characters in Excel

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,

  1. Open your Excel file.
  2. Insert a column to the right of the field you wish to count.
  3. Enter a descriptive label for the column. For example, I used Dcount since I want to count the characters.
  4. Place your cursor in the first cell under that column label. In my example, it’s D2.
  5. From the Formulas tab, click Insert Function…
  6. In the Insert Function dialog, select the category of All.
  7. Select the LEN function from the list. (Tip: you can press L to advance the list.)
Insert Function dialog with LEN in search field.
Finding and inserting the LEN function
  1. Click OK. The Functions Arguments dialog should appear.
  2. 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.
Function Arguments dialog with cell C2 in Text field.
Adding the function argument of cell C2
  1. 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.

  1. Place your cursor in the first cell with the LEN formula.
  2. Scroll down with your mouse and Shift + click in the last cell in your column
  3. 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.

LEN function counting trailing spaces on address.
Example of trailing spaces being counted

Troubleshooting

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

  1. 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.
  2. 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.