Sorting: How to get A2 come before A10

**boka** · 02-16-2010, 06:58 PM

Hi,

I have a spreadsheet with a row that contains a certain code in string+number format. Example: (actually in random order, A4 may come before A1)

A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
AA1
AA2
....
AA10
AA11

Then if I try to sort it sequentially, that is A1 A2 A3 ... A10 A11; It sorts like A1 A10 ... A11 A2 A20 .... this way.

What can I do to make it sorted the way I want ?

**teylyn** · 02-16-2010, 07:12 PM

Hi,

the values you are sorting are text. The order for text sort is A1, A11, A2, etc. If you want a numeric sort, you either need to change your text values so the numbers have leading zeros

A001
A002
A003
A010
A020

or you need a separate column with only numeric values and sort the data based on that column.

**martindwilson** · 02-16-2010, 07:18 PM

what does your real data look like?
to get that to sort you need to make the numbers the same length
eg
A01
A02
A03
A04
A05
A06
A07
A08
A09
A10
A11
AA01
AA02
then use that to sort by but method will vary according to data
one way
=SUBSTITUTE(A1,LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),"")&TEXT(LOOKUP( 9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),"0000000")

**boka** · 02-16-2010, 07:19 PM

adding leading zeroes is not currently (manually I mean, at least in current format) an option since there are more than thousand rows, unless I know any formula that does it.

The number of string characters vary (from 1 to 6). Numbers do not have same format (that is not in 01 02 format, the actual data is in 1 2 3 .. 10 11 format).

Any available options for me?

I have added an extract from the spreadsheet. SetCode is the variable/column I want to sort in A1 A2 ..A10 format.

**shg** · 02-16-2010, 07:21 PM

See the function PadNums at http://www.excelforum.com/excel-prog...eric-data.html

**boka** · 02-16-2010, 07:26 PM

tried this B1: =LOOKUP(9.99E+307,--MID(A1,1,ROW(A$1:INDEX(A:A,LEN(A1)))))

I get #N/A in B column.

I too noob in excel to know/read a vba function.

**shg** · 02-16-2010, 07:28 PM

This: http://www.excelforum.com/2249322-post5.html

**boka** · 02-16-2010, 07:29 PM

this works ....=SUBSTITUTE(A1,LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),"")&TEXT(LOOKUP( 9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))),"0000000")

Thanks !!