Hello All,
I've attached a sample spreadsheet. I have two questions:
#1 When I sort the spreadsheet from smallest to largest, by case id, shouldn't the cases with numbers like 111001111-01 be first followed by these... 20000000234568?
#2 When I try to do a custom sort by shelf then by case id, excel treats the case id numbers as text. Why? I've formatted the cells as numbers.
Are my sorting problems due to the fact that some of the cases end in "-01"?
Thank you.
Put this formula in C3, then copy down:
=ISTEXT(A2)
See the problem?
If you type a dash - into a cell, the cell becomes text, plain and simple. Text always sorts after numbers.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I see that, it returns a "true" on my "-01" numbers and it does make sense. However, I have another spreadsheet with similar case number and that one does sort properly but when I added the formula on that speadsheet, it returned a value of true for all of the numbers even though they are formatted as numbers. I wish I knew what the difference was between those two spreadsheets.
Thank you for your help.
Even though your cells may be formatted as numbers, the "number" inside the cell might be formatted as text. This can happen if
1. you change the formatting after the number/text is already in the cell
2. there are spaces or other characters that cause Excel to treat the number as text.
Hope that helps. You'll need them to all be numbers or (if they all have the same # of digits) all be text.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
the difference should be visible to you as you click on the cells themselves and look at the formula bar to see what is REALLY in those cells. If there REALLY is a dash - in that cell, then it will be a text string. But you can use "formatting tricks" to make a dash "appear" in the cell when it's not really there as shown in the formula bar.
Thanks for the feedback. If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.
What kind of tricks and will that make my numbers actually sort from smallest to greatest?
They ARE sorting from smallest to greatest. Your mind is giving more context to the values than Excel does. Numbers then text, that's how it sorts.
You can resort to inputting all your strings as text, that might get you closer to what you want. For the numbers, put an apostrophe in the cell ahead of the number, that makes it a text string.
'20000001111234
'20000001234567
'20000000234568
111001111-01
111001234-01
Then when you sort, they're all text, so they'll do better.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks