Sorting Data, Text and Numeric

[Shorty]

Hello,
I have a sheet of data I need sorted according to a value in a column
(C), which is both text and numeric. You can see below that the C
column doesn't sort the rows correctly as BG 10 should be after BC9

A B C

18 3:46 AR 7
19 4:24 AR 8
20 5:05 AR 9
21 2:49 BG 1
22 5:41 BG 10
23 3:08 BG 2
24 3:28 BG 3

The C column data always has two letters before the number (i can make
it with or without the space before the number) I don't really want
to have to use another column to store any values as it is already an
overly complicated spreadsheet.
Please could anyone help me with this? All reply's appreciated!
Shorty

 

[davesexcel]

18 3:46 AR .7
19 4:24 AR .8
20 5:05 AR .9
21 2:49 BG .1
23 3:08 BG .2
24 3:28 BG .3
22 5:41 BG 10

All I can think of is using decimals to get the proper sor

 

[yngve]

Hi

as fare as I now you have to use texttocolumns, here is an sample, I
have recorded it and change som cod`s "I USE EXCEL 2003"

Sub Makro2()
Application.ScreenUpdating = False
Columns("E:G").Insert Shift:=xlToRight

Columns("D").TextToColumns Destination:=Range("D1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(10, 1)),
TrailingMinusNumbers:= _
True

Rows("5:11").Sort Key1:=Range("E5"), Order1:=xlAscending,
Key2:=Range("F5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Columns("D").Insert Shift:=xlToRight
Range("D511").FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[2],""
"",RC[3])"
Range("D511") = Range("D511").Value
Columns("E:H").Delete Shift:=xlToLeft
Application.ScreenUpdating = True




End Sub

rwgards Yngve

 

[Shorty]

Hi,
Thanks for that, ive give it a quick go swapping the space with a
decimal point '.' but i still cant get it to sort, am i missing
something still?

 

[Shorty]

The easiest way ive found is just to add a '0' to the lower numbers,
ie:

86 4:57 NG08
87 5:17 NG09
88 5:33 NG10
89 1:01 NR01
90 1:37 NR02
91 2:12 NR03

though i dont really want the 0 in the data its the easiest way to do
this. if anyone knows of a better work round or the real method to sort
this keep the posts comming!

 

[Jim Cone]

Since you asked "if anyone knows of a better work round"...

Using a helper column with the digits padded with zeros is
the only way I know to do what you want.
An alternative is my Special Sort Excel add-in that can sort
by the last set of numbers.

either...
18 3:46 AR 7
19 4:24 AR 8
20 5:05 AR 9
21 2:49 BG 1
23 3:08 BG 2
24 3:28 BG 3
22 5:41 BG 10

or...
21 2:49 BG 1
23 3:08 BG 2
24 3:28 BG 3
18 3:46 AR 7
19 4:24 AR 8
20 5:05 AR 9
22 5:41 BG 10

The drawback is that is a commercial application.
You can read a review of it here...
http://www.officeletter.com/blink/specialsort.html

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



in message...
The easiest way ive found is just to add a '0' to the lower numbers,
ie:
86 4:57 NG08
87 5:17 NG09
88 5:33 NG10
89 1:01 NR01
90 1:37 NR02
91 2:12 NR03
though i dont really want the 0 in the data its the easiest way to do
this. if anyone knows of a better work round or the real method to sort
this keep the posts comming!