I see this problem a lot when people install new systems. In her case, it was one of those impasses where the developer kept pointing her to the record layout. Her complaint was the new system should identify which imported record caused the problem. Both of them are right, but that doesn’t solve the immediate problem. She was dealing with thousands of records and to go through this problem one failure at a time would be unproductive.
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. 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.
To use Excel’s LEN function,
1. Open your Excel file.
2. Insert a column to the right of the field you wish to measure.
3. Enter a descriptive label for the column. I used Dcount since I want to count the characters in my Description column.
4. Place your cursor in the first cell under that column label.
5. From the Insert menu, select 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.)
8. Click OK. The Functions Arguments dialog should appear.
9. 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 portion.
10. 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 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.
Related Excel Tutorials:
Last Updated (Friday, 18 June 2010 17:18)