Macro for sorting alpha-numeric data

[drewbagel423]

In column A I have alpha-numeric data similar to:

2
A1
3
A20
A186
B4
B13
1
C5
C3

And I need to sort them so that they are like:

1
2
3
A1
A20
A186
B4
B13
C3
C5

With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.

TIA

[rylo]

Hi

1) Is the alpha component always 1 character long?

2) Do you have a blank couple of columns that you can use as assists. I think you are going to have to separate the alpha and numerics, then sort separately, then recombine.


rylo

[drewbagel423]

Hi

1) Is the alpha component always 1 character long?

2) Do you have a blank couple of columns that you can use as assists. I think you are going to have to separate the alpha and numerics, then sort separately, then recombine.


rylo

1) No, it may be 2 or more.

2) That's ok, I would just need to delete them at the end of the macro.

[shg]

If your comfortable using a UDF, you can add this:

Please Login or Register  to view this content.

Example usage is =PadNumbers(A1, 3) in a separate column, then sort by that column.

[rylo]

Hi

For the moment, assume you have you data in column A, starting in row 2 (A1 is a heading) and you can use columns B and C as working columns.

See how this goes.

Please Login or Register  to view this content.

rylo

[drewbagel423]

Seems like it worked, thanks.

[sy3d]

Hi

For the moment, assume you have you data in column A, starting in row 2 (A1 is a heading) and you can use columns B and C as working columns.

See how this goes.

Please Login or Register  to view this content.

rylo

i've data from columns A1:L1
if i sort data of A1, then data of columns B1 to L1 should be adjusted as per sort of column A
can you help
thx