Sorting Data with Dashes

[Tacone]

I am very new to VBA and I am alone here, so there is no one I can go to for advice.

I have a spreadsheet that has a column formatted as Text. The cells contain mostly numeric data, many of which begin with zero. My problem arises when I encounter data with a Dash in it. The Sort function ignores the Dash, but the VBA Compare does not. So VBA determines that the data is out of sequence. 1234 sorts in front of 1-345, but 1234 compares larger then 1-345.

Is it possible to perform a VBA Sort that will produce output that compares as being in sequence?

Thank You for any assistance you can provide, and Thank You for your time.

 

[CautionMP]

Tacone,
[tt]Binary Compare:[tab]"1234" > "1-345" = True
Text Compare :[tab]"1234" > "1-345" = False[/tt]

Two options[ol] [li]Add a [tt]Option Compare Text[/tt] statement to the top of your module.[/li][li]Use StrComp("1234", "1-345", vbTextCompare)[/tt] to force a textual comparison.[/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Tacone]

Thank You for the advice. I was up against a wall and had no place to go.