This tutorial shows how to use Excel’s LEN function. A reader posed an interesting conversion problem between an old database and a new online one. The new online system would not accept field values longer than a set number of characters. In this case she needed to know how many characters were in a cell.
I suggested she use Excel as an intermediate solution. To spot items that are too long, you can use one of the built-in Excel functions called LEN. It returns the number of characters for a specific 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. You could also sort on the length column as well.
Instead of showing this company’s files, I’ll use an example I encounter. When I enter an article description in my content management system, it doesn’t provide a character count. If I make the Meta description too long, the search engines truncate my text and place … at the end. This doesn’t break Google, but it’s not the best user experience. Periodically, I’ll export the data and have Excel look for records that exceed a certain length.
Find Character Count with LEN,
- Open your Excel file.
- Insert a column to the right of the field you wish to measure.
- Enter a descriptive label for the column. I used Dcount since I want to count the characters in my Description column.
- Place your cursor in the first cell under that column label.
- 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 dialog. In my example, it would be A2. You can also see the Excel formula result in the lower part.
- 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 Description field. 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.
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 like converting records. It can be a great intermediary tool for spotting problem records or converting data.
Want More Tips & Tools?
We also publish a free newsletter with relevant stories, tips and special offers.