5 Minute Tips General How to Use Excel's LEN Function to Find Problem Records | How to Use Excel's LEN Function to Find Problem Records |
|
| Tuesday, 07 August 2007 | |
|
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. Rather than getting an error message saying which record was the problem, a generic import failure message appeared. As you may have guessed, the old system didn’t have length restriction on fields. The good news is the developer provided a record layout with field sizes. With this info, she could use Microsoft Excel's LEN function to identify and correct the problem records before importing to the new database. (Includes online tutorial)
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.  Click to enlarge 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.)  Click to enlarge 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.  Click to enlarge 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 Tutorial:
Related Article:
|
|
| Last Updated ( Thursday, 04 October 2007 ) |
