Please help - sorting list of numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ivoryblue1 - 16 Aug 2006 17:46 GMT

Hello,

I am not so good with using excel and nobody in my office can figure out
what I'm doing wrong.

I am trying to sort a list of folders first by Box Letter and then by Folder
Number. I want my list to look like this:
Box Folder
A 1
A 2
A 3
B 1
B 2
B 3
and so on.

But whenever I sort the information, the numbers are all out of order. My
list looks more like 1, 10, 11, 12, 2, 21, 22, 3, etc. I know if I were to
insert a 0 before each single digit number, it would sort correctly, however
there are several thousand entries on this list and I don't want to go
through and add several thousand 0's. I think it has something to do with
the formatting of my columns, but I've changed it to everything available and
still it sorts incorrectly. What else can I do?

Reply to this Message

David Billigmeier - 16 Aug 2006 17:52 GMT

It's because your numbers are formatted as text. Simply doing a
Format->Cells->Number doesn't fix unfortunately. Follow these steps to get
it working:

Copy an empty cell
Select your range of numbers
Right click choose "Paste Special"
Select "Add" and hit OK

Signature

Regards,
Dave

> Hello,
>
[quoted text clipped - 19 lines]
> the formatting of my columns, but I've changed it to everything available and
> still it sorts incorrectly. What else can I do?

Reply to this Message

Bob Umlas - 16 Aug 2006 17:53 GMT

I'd bet the values (1,2,3...) are TEXT values, not numeric. Reformat as
General, then copy an unused (empty) cell & paste special values & "Add" to
the range & try again.
Bob Umlas
Excel MVP

> Hello,
>
[quoted text clipped - 19 lines]
> the formatting of my columns, but I've changed it to everything available and
> still it sorts incorrectly. What else can I do?

Reply to this Message