Sort all values in a column with Alphanumeric without adding zeros

[sampathmeka]

Hi All,

Below is the example data i need to sort alphanumeric values.
C1
C100
C1000
C2
C23
C3
C31
C4
C8
C9
C99
A10
A11
A12

The output should be
A10
A11
A12
C1
C2
C3
C4
C8
C9
C23
C31
C99
C100
C1000


Thanks & Regards,
Sampath

 

[sandy666]

with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitPos = Table.SplitColumn(Source, "raw", Splitter.SplitTextByPositions({0, 1}, false), {"raw.1", "raw.2"}),
    Type = Table.TransformColumnTypes(SplitPos,{{"raw.1", type text}, {"raw.2", Int64.Type}}),
    Sort = Table.Sort(Type,{{"raw.1", Order.Ascending}, {"raw.2", Order.Ascending}}),
    Join = Table.CombineColumns(Table.TransformColumnTypes(Sort, {{"raw.2", type text}}, "en-GB"),{"raw.1", "raw.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"raw")
in
    Join

raw		raw
C1		A10
C100		A11
C1000		A12
C2		C1
C23		C2
C3		C3
C31		C4
C4		C8
C8		C9
C9		C23
C99		C31
A10		C99
A11		C100
A12		C1000

 

[mamady]

This can be done with the following formula as well

assuming the values are in range A1:A14
=SORTBY(A1:A14,LEFT(A1:A14,1),1,--RIGHT(A1:A14,LEN(A1:A14)-1),1)

Regards

 

[sampathmeka]

This can be done with the following formula as well

assuming the values are in range A1:A14
=SORTBY(A1:A14,LEFT(A1:A14,1),1,--RIGHT(A1:A14,LEN(A1:A14)-1),1)

Regards

I tried with the above formula but not acheived.Kindly help me.
Thanks for reply Mamady!!

 

[sandy666]

that means SORTBY() function is not supported by your Excel version

 

[CA_Punit]

We need Expert advice @Eric W With formula

 

[Eric W]

sampathmeka,

What version of Excel do you have? It would be helpful if you would update your user profile to show that. And to clarify your question, will there always be just 1 letter in front? Will case matter? How many digits can the number be? Are you looking for a formula or a macro? Depending on the answer to these questions, you could make a couple of helper columns as shown below, and then just use the built-in Excel sort on columns C and D.

Book1
	A	B	C	D
1	A10		A	10
2	A11		A	11
3	A12		A	12
4	C1		C	1
5	C2		C	2
6	C3		C	3
7	C4		C	4
8	C8		C	8
9	C9		C	9
10	C23		C	23
11	C31		C	31
12	C99		C	99
13	C100		C	100
14	C1000		C	1000
Sheet2

Cell Formulas
Range	Formula
C1:C14	C1	=LEFT(A1)
D1:D14	D1	=MID(A1,2,9)+0

 

[CA_Punit]

.

 

[CA_Punit]

Out of curosity i did this. I don't know how to shorten it.

Sorting Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	raw
2	B1
3	C100
4	C1000
5	C2
6	B2
7	C3
8	C1
9	C4				A1	A2	A3	A4	A8	A9	A11	A12	A99	A100	A1000
10	C8				B1	B2	B3	B4	B8	B9	B11	B12	B99	B100	B1000
11	C9				C1	C2	C3	C4	C8	C9	C11	C12	C99	C100	C1000
12	C99
13	A1000
14	A11
15	A12				A11
16					A12
17					A1000
18					B1
19					B2
20					C1
21					C2
22					C3
23					C4
24					C8
25					C9
26					C99
27					C100
28					C1000
Sheet1

Cell Formulas
Range	Formula
E9:O11	E9	=INDEX(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),"")))))),MATCH(ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),"")))),MMULT(--(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),""))))))>TRANSPOSE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),"")))))))),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),""))))^0)+1,0))&TRANSPOSE(INDEX((INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,""))))))),MATCH(ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,"")))),MMULT(--(INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,""))))))>TRANSPOSE(INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,"")))))))),ROW(INDIRECT("A1:A"&COUNT((INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,""))))))))))^0)+1,0)))
E15:E28	E15	=IFERROR(INDIRECT(TEXT(SMALL(IFERROR(((MATCH(INDEX(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),"")))))),MATCH(ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),"")))),MMULT(--(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),""))))))>TRANSPOSE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),"")))))))),ROW(INDIRECT("A1:A"&COUNT(IFERROR(CODE(INDEX(LEFT($A$2:$A$15,1),AGGREGATE(15,6,FREQUENCY(MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0),MATCH(LEFT($A$2:$A$15,1),LEFT($A$2:$A$15,1),0))^0*ROW($A$1:$A$15),ROW($A$1:$A$15)))),""))))^0)+1,0))&TRANSPOSE(INDEX((INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,""))))))),MATCH(ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,"")))),MMULT(--(INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,""))))))>TRANSPOSE(INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,"")))))))),ROW(INDIRECT("A1:A"&COUNT((INDEX(MID($A$2:$A$15,2,1000)+0,AGGREGATE(15,6,(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0)*ROW($A$1:$A$20),ROW(INDIRECT("A1:A"&COUNT(IFERROR(FREQUENCY(MID($A$2:$A$15,2,1000)+0,MID($A$2:$A$15,2,1000)+0)^0,""))))))))))^0)+1,0))),$A$2:$A$15,0)^0)*ROW($9:$11)*100)+COLUMN($E:$Q),FALSE),ROWS($A$1:A1)),"R00C00"),FALSE),"")
Dynamic array formulas.

 

[CA_Punit]

If you dont use office 365.