Sorting Letters and numbers, and the 10 before 2 problem!

[MrB]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

Hi Guys,

I have a column with data such as A1, A2 etc to A12 then B1,B2 to B12, you get the picture. Because of other data in the sheet it is entered out of sequence but i would like the option to see it in sequence.

When i use autofilter i get A1, A10, A11, A12, A2 etc. How can i make it read A1, A2 and so on? I can enter that data in a different format if that would make it work.

Thanx in advance for any help

 

[PhilD]

When i use autofilter i get A1, A10, A11, A12, A2 etc. How can i make it read A1, A2 and so on? I can enter that data in a different format if that would make it work.

One possibility is to change A1 to A01, A2 to A02, and so on up to A9
(A09). (A search and replace may help if you have lots of the same).

It's a bit cumbersome, but depending on how much you need to do this
may help.

PhilD

 

[JE McGimpsey]

I can enter that data in a different format if that would make it
work.

Try

A01
A02
...
A09
A10
A11
etc...

 

[MrB]

Hi PhilD and J.E. Mc Gimpsey,

Thanx for that suggestion. I'll give it a go. I looked it up in the manual for excel and it warns you about this problem yet doesn't come up with a solution, surely if they know people want to do this, they'd offer an option to wouldn't they!?

Thanx again for your help

Mark B

 

[JE McGimpsey]

I looked it up in the manual for excel and it warns you about this
problem yet doesn't come up with a solution, surely if they know
people want to do this, they'd offer an option to wouldn't they!?

I assume by "manual" you mean XL Help.

Since you're using XL08, you have an excellent method of helping your
fellow users. If you've enabled On-line Help, you can click the
"Somewhat" button at the bottom of the topic, and suggest that they
offer an example solution. That may then at some point end up being
incorporated into on-line Help!

 

[Carl Witthoft]

I assume by "manual" you mean XL Help.

Since you're using XL08, you have an excellent method of helping your
fellow users. If you've enabled On-line Help, you can click the
"Somewhat" button at the bottom of the topic, and suggest that they
offer an example solution. That may then at some point end up being
incorporated into on-line Help!

This may not help the OP, but in cases where there is only one type of
prefix, e.g. the data are of the form Name1, Name2, ...Name44.5,
Name329.12 , and so on, I enter the numbers only and format the cell's
NumberFormat as "Name"0.00 .That way the numbers sort properly and the
display looks right too.

But, no way to do that if he's got A1, B2, and so on. What he really
needs is a CustomSortOrder defined as something like (regexp-ish
notation) [A-Z0-9] .
In fact if you are capable of converting the cell's contents to the
ASCII hex numbers, sorting on that, and converting back, that might
work ^_^