Sort to include hyphen

[mcolson]

I'm having a problem with sorting my Data. When Excel does a sort, it
does not include hyphens. So MV-2 would come after MV1. But later,
when I try to import new data, I am doing a binary search. In this
search, MV-2, would be seen as less than MV1, because a '-' is less
than 1. Is there any way to do a quick sort that includes the '-' in
the search criteria?

Thanks,

Matt

 

[Dave Peterson]

How about using another column of cells with a formula in it and sort your range
on that.

The code for the hypen is 45 (=code("-"))
The code for a dot is 46 (=code("."))

Maybe:
=substitute(a1,"-",".")

It may cause some confusion if you have items like MV-1 and MV.1, though.

 

[mcolson]

How about using another column of cells with a formula in it and sort your range
on that.

The code for the hypen is 45 (=code("-"))
The code for a dot is 46 (=code("."))

Maybe:
=substitute(a1,"-",".")

It may cause some confusion if you have items like MV-1 and MV.1, though.

Thanks, that should work.