Tech Tips Home
The Best Tech Tips And Daily Deals

WorldStart Tech Tip And Store Search

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

# Finding Excel Record Lengths

Tuesday, November 16th, 2004 by | Filed Under: MS Excel

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.

Poof!

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.

Click OK.

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!)

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.

Click OK.

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!

~ April

• Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

• WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

• Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.

Subscribe

We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.

Subscribe

Categories:
Archives: