Finding Excel Record Lengths
Here’s a tip I received from a reader who finds Excel’s function for determining data length (number of characters in the data) to be a useful tool.
Maybe you too have data sets in Excel through which you must search to find the longest or shortest element.
At any rate, I thought it might be a good idea to pass this one along so let’s get to business.
The obvious first step is to open the file containing the data.
Select a cell on the same row as the top of the data in an unused column. (Next to the data is ideal for quickly copying the formula—but not absolutely necessary.)
The formula you’re looking for is =LEN(A1) where A1 should be replaced with the address of the cell containing the data. For example: if the long records are in column C and begin in row 1 then type in =LEN(C1). (Please note that you must have the = sign in front so Excel knows that it’s a formula.)
Hit the Enter key.
In the cell you should now see the length of the data (including spaces) displayed.
You ask “Do I have to do this for each row?”
Fortunately the answer is a resounding “No!”.
There’s always the old copy / paste and all of the shortcuts we love to accumulate.
If the formula is in a column adjacent to the data range then you could put Tuesday’s tip to good use this very minute. Simply double-click on the cell’s fill handle and you’ll instantly have data lengths displayed all the way to the bottom.
(Just as a reminder…)
If the formula is in a column not adjacent to the data then click, hold and drag the fill handle down the length of the data. Release the mouse button at the end—you too will instantly have the length of you data items calculated and displayed.
(There are other ways, as we all know, these are just two very quick suggestions to get you on your way.)
Now for the fun part—you can sort on length data. This allows you to quickly find the longest or shortest piece of data without manually searching through the column.
The catch? (You knew that there had to be one, didn’t you?)
Yeah, there’s a catch—you must first change the formula to an actual value.
Ready to convert formulas to values?
Begin by highlighting ALL the values (formulas) in the newly added column. Now right click and choose the Copy choice from the menu (Ctrl + C would work too).
At this point you should right click again and choose Paste Special.
When the Paste Special window opens click on the radio button for Values.
This will replace the formula (invisible) in the cell with the actual number that you see in the cell.
At this point, when you select a cell containing a data length you should no longer see the formula in the Formula Bar. The cell truly contains only the numbers. (Bye-bye formulas!)
Now you’re ready to move forward with your sort.
Begin by highlighting all the data in the columns that you want to sort, including the length data. (In an address file, for example, you would highlight ALL columns with records since you want to keep the addresses intact.)
You’ll find sorting in the Data menu, Sort choice. Choose to sort either ascending or descending on the column with the length data.
*Important Note Here… If you have headers highlighted and want to keep them at the top of the file make sure the radio button at the bottom next to “Header Row” is selected.
Voila! Your data is in perfect order based on data length.
This seems very complicated but after you do it a few times, it is rather easy.
If you make a mistake with the sort don’t forget about your old friend the Undo feature and the file will be back to the way it was.
A quick suggestion: If you liked the previous order of the file but wanted just to find the long records, do the sort, make a note of the records of the length you need (or print them) and then undo the sort. Use the Edit menu, Find function to get to the records and change them.
Thanks go out to Randy, the reader who took the time to share his expertise!