alphanumeric sorting

[Guest]

Hello there,
I have this little prob in Excel:

Alphanumeric Sorting is done from left to right, as default, in Excel:

"Alphanumeric sort When you sort alphanumeric text, Excel sorts left to
right, character by character. For example, if a cell contains the text
"A100," Excel places the cell after a cell that contains the entry "A1" and
before a cell that contains the entry "A11."

I want to change that. Is there a way to treat the "A" as irrelevant and so
that A11 actually comes between A1 And A100?

Thx a bunch in advance,
DK.

PS: i've read the previous responses, but i thought it was too specific. I
want to know if i'm missing sthing obvious here. Is there a simple command to
switch the alphanumeric sorting?

 

[Guest]

One work-around would be to extract the left-most character, A, then sort the
numbers, and re-concatenate the sorted strings.

Example:

If cells A1:A100 contain A1...A100, then in column B put the formula
=RIGHT(A1,3) to extract the numbers, copy, paste special values, sort that
column numerically, and then in column C: =CONCATENATE(A1,B1) and fill down.

Of course there may be a more elegant way.

I think you're running into a quirk in the way Windows (not Excel) sorts
data. You can see the same issue in folders with dozens of files.