Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel97 problem with sort

Excel97 problem with sort

Excel97 problem with sort

I sometimes experience problems trying to sort columns that contain numbers formatted as text. The sort either doesn't work at all or sorts in several blocks. I've made sure that there are no leading or trailing spaces or special characters. Any ideas?

RE: Excel97 problem with sort

Barry: Can you give me an example? Also, why are you formatting them as text? Perhaps that will explain for me why they don't sort.


RE: Excel97 problem with sort

I use Excel to manipulate downloads from our main system, SAP. Some values have leading zeroes. If I accept the default format, General, leading zeroes are stripped off. Text retains the zeroes.

An example of the problem:

D      174
D      200
D      082
B      200
B      190
D      190

If I sort this by column A asc, column B asc, it should result in:

B       190
B       200
D       082
D       174
D       200

but doesn't. Sometimes it changes the order, but incorrectly and sometimes it doesn't change the order at all.

Thanks for you help with this.

RE: Excel97 problem with sort

Okay, if you want to keep exactly 3 digits, then format as numbers. Then, format-special and type 000 in the box for the special formatting. This works great when you want check numbers or account numbers or whatever to have leading zeroes. And it might even sort properly for you. Give it a try.


RE: Excel97 problem with sort

If you can import the numerical data as numerical and then use custom format as stated by Dreamboat, that would be the best solution.

However, If you Must import the numbers as text, there is another solution. Actually more than one.

Assuming your data is in A2:B7, in C2 ad the formula =VALUE(B2) and drag it down to C7. Now sort A2:C7 using Key1 = A and key2 = C. You'll have the sort you want. You can then delete, leave as is, or hide column C.

Bob Crawford

RE: Excel97 problem with sort

Just one other thing worth noting--I encountered the same problem with the numbers that started with zeroes (made it hard to verify serial numbers because it lost the first number).  If you put a single quotation mark (') in front of the number, it won't drop the zero.

Linda Adams
Linda Adams Online
I'm a professional writer, published internationally.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close