Sort numbers prefixed with #

[smithgt]

I have a sheet exported from another app that has a number column pre-fixed with the # symbol

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64>#1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>#10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>#14</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>#2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>#4</TD></TR></TBODY></TABLE>

Can I get this sorted correctly without altering the data?

thanks

 

[JackDanIce]

I don't think you can, but why can't you highlight the whole column, use search and replace to remove all the #tags and then sort and then using a helper column reattach the #tags, e.g.

="#"&A1

 

[MickG]

Try this:-
Data column "A"

[COLOR=navy]Sub[/COLOR] MG06Jun00
[COLOR=navy]Dim[/COLOR] Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] J [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ray
Ray = Application.Transpose(Range(Range("A1"), Range("A" & rows.Count).End(xlUp)).value)
[COLOR=navy]For[/COLOR] i = 1 To UBound(Ray)
    [COLOR=navy]For[/COLOR] J = i To UBound(Ray)
        [COLOR=navy]If[/COLOR] Val(Right(Ray(J), Len(Ray(J)) - 1)) < Val(Right(Ray(i), Len(Ray(i)) - 1)) [COLOR=navy]Then[/COLOR]
            Temp = Ray(i)
            Ray(i) = Ray(J)
            Ray(J) = Temp
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] J
[COLOR=navy]Next[/COLOR] i
Range("A1").Resize(UBound(Ray)) = Application.Transpose(Ray)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick