Sorting numbers a specific way?

[Dno]

Hi,

I was wondering how to sort numbers that I think are formated as text and
the reason for being sorted the way they are in the example below.

I have a column that sorts in the following format:

A1
A10
A11
A12
A13
A14
A15
A16
A17
A18
A19
A2
A21
A22
A23
B1
B10
B11
B12
B13
B14
B15
B16
B17
B18
B19
B2
B20
B21

I'd like to make it so numbers are sorted like this:

A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
A15
A16
A17
A18
A19
A20
A21
A22
A23
B1
B2
B3
B4
B5
etc



So number 2 would go after 1 instead of after 19.

A GIS program I use sorts numbers this way and I'd like to sort them properly to
better work with the data.

Thanks!

Dino

EDITED: ADDED LETTERS IN FRONT

[sweep]

Hi,

One way you can convert your "text" to numbers is to type 1 into a blank cell, select the cell, copy it, select your "text" range, edit > paste special > multiply.

You can then use the sort function to get the data numerically ordered.

[Dno]

Thanks Dave =)

One thing I left out is my column actually really
looks like the following and I'm working with existing data in a dbf file.

A1
A10
A11
A12
A13
A14
A15
A16
A17
A18
A19
A2
A20
A22
A23

[sweep]

OK, so you could use excel to extract the number into the column next to your list, and then sort by that column?

=RIGHT(A1,LEN(A1)-1) will give you the numerical part of your data

[Dno]

Basically want to now do the opposite of this post
http://excelforum.com/showthread.php?t=590869

EDIT: oops meant this above link

[Dno]

Thanks for that idea as well. It work but I also have other letters
in front of the numbers like the following. Sorry I was trying to simply the question and left out a few things.

B1
B2
B3
C1
C2
C3

I need to keep the letters in order as well.

[ChemistB]

It's getting complicated now but you could continue with what Sweep suggested and have two dummy columns, one with the numbers using the formula Sweep gave and a second with the letters

Please Login or Register  to view this content.

Sort first by the column with the letters, with a secondary sort on the numbers. Other than that, I think you'd need to use VBA.

ChemistB

[Dno]

Thanks Chemist,

Right getting a bit complicated that way but I was thinking there must be a way to easily do it since I can do the opposite thing by putting in a helper column =Text(A1,0) and sort on the helper column with 'separately' to the 'Numbers' question

[ChemistB]

You can put this formula into the helper column and sort on this.

Please Login or Register  to view this content.

That converts your letter value to it's numerical ascii equivalent and then multiplies it by 100. This ensures that has precedence over the numerical values (unless your numerical values exceed 100, then multiply by 1000). You can sort on the resulting number.

ChemistB