Letters and 000s in front of actual number not sorting correctly

[darkjedi]

When sorting numbers in excell with letter in front and then zeros, I
don't get the expected results. The Macintosh OSX finder views the
numbers correctly, but I can't seem to get the same results in excel.
Can this even be done in excel?

Excell sort
CA0002
CA00020
CA0021
CA02
CA021
CA02C
CA2

Needs to be
CA0002
CA02
CA02C
CA2
CA00020
CA0021
CA021

 

[Dave Peterson]

I would think the best way would be to separate the component parts into
separate cells.

CA 2 C

With that numeric column treated as numbers.

Then select the whole range and sort by that middle helper column, then first
helper column, then last helper column.

But this kind of sort would keep:
CA0002
CA002
CA02
CA2

in the same order as the original data.

I'm not sure what the rules are when you have the numeric portion all the
same--maybe a fourth helper column based on the length???