Solved: Alphanumeric Sort

[joms]

hi, would like to sort an alphanumeric data, can anyone help on how to achieve this..basically, i have a raw data like C1, C10, C100, C11, C12, etc..so when sorted it's output should be in ascending order .. like C1, C10, C11, C12, C100..Etc.. thanks in advance... my data is like this:


RAW DATA
C1
C10
C102
C103
C104
C105
C106
C107
C108
C109
C11
C110
C100
C111
C112
C113
C114
C115
C116
C117
C118
C119
C12
C101
C120
C122
C1200
C121

[mdmackillop]

I would convert your raw data to a sortable format eg C0001
This should help accomplish that task, (assuming your data is representative)
=LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1),"0000")

[joms]

Hi, mdmackillop..thanks for the reply..i tried to modified the formula that you suggested as: =(RIGHT(A2,LEN(A2)-1)) and =LEFT(A1,1), so i can separate the letter and the number..but when i tried to sort the number it shows this error..and i have tried formatting the cell to a number but it doesn't help... any ideas? thanks..
here's the print screen of the error..

[joms]

Hi, mdmackillop, thanks i find a work around on this.. the formula you suggested helps a lot...what i did was like this:
=RIGHT(A1,(LEN(A1)-1)) 'to get the numbers
'then multiply the data to 1 to convert it to numbers then sort using excel
=LEFT(A1,1) & TEXT(RIGHT(B1,LEN(B1)),"0") 'to combined the letters again
it sorts perfectly..

but is there a simpler way to do this? thank you..

[Aussiebear]

Hi joms, The thing to remember here is that your initial data was stored as Text, and that it will remain as Text even after removing the alphabetical 1st character.

Using the data in your graphic as an example:
=Left(A1,1) on C101 will return C

whereas using
=Right(A1,Len(A1)-1 on C101 will return 101 ( the 101 value is still regarded as text. See in your example how the string is stored to the left of the cell?

However using
=Right(A1,Len(A1)-1)*1 on C101 will return 101 as a number, which will be stored to the right side of the cell.

[GTO]

Greetings Joms,

Not sure if this is of any help, but if you cannot convert the data, maybe a short macro? This would still require the helper column, so Col B would be used (or temp inserted/deleted)

[vba]
Option Explicit
Sub EX()
Dim lLRow As Long
Dim rng As Range
Dim ary
Dim i As Long

'// Starts in row 2, change to suit.//
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

ary = Application.Transpose(rng.Value)

For i = LBound(ary) To UBound(ary)
ary(i) = Right(ary(i), Len(ary(i)) - 1)
Next

rng.Offset(, 1).Value = Application.Transpose(ary)

Set rng = rng.Resize(, 2)
rng.Sort Key1:=rng(1, 2), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Set rng = rng.Resize(, 1).Offset(, 1)
rng.ClearContents
End Sub
[/vba]

Hope this helps,

Mark

[joms]

hi, mdmackillop, Aussiebar and GTO...thanks for the help.. Cheers

[mdmackillop]

Hi, mdmackillop, thanks i find a work around on this.. the formula you suggested helps a lot...what i did was like this:
=RIGHT(A1,(LEN(A1)-1)) 'to get the numbers
'then multiply the data to 1 to convert it to numbers then sort using excel
=LEFT(A1,1) & TEXT(RIGHT(B1,LEN(B1)),"0") 'to combined the letters again
it sorts perfectly..

but is there a simpler way to do this? thank you..

Yes.
Just use the formula I gave you to put the data in one column and sort on that column, unless of course your data is not really as presented.