Hyphen Minus Dash

I was working in Excel matching some strings and they were not matching even though they looked the same on the screen.

Well it turns out that there are different kinds of dashes!

A normal dash you use is the keyboard is the minus sign on your keyboard. But there are others that can get put in. In my case there were some normal minus characters but there were also some actual dash characters.

The difference as described in the link above is the familiar “-”

Ascii hyphen, with multiple usage, or “ambiguous semantic value”; the width should be “average”.

But somehow in the data there was also this character, “‐” which is

unambiguously a hyphen character, as in “left-to-right”; narrow width.

It took some digging to figure this out, looking at the boolean values in the text to see what characters were there.

So in order to spare me future dash grief, I put together the following Excel function. It’s self explanatory.

Note that it also deletes spaces as I don’t want any leading or trailing ones in the strings I’m dealing with in Excel. You could comment those lines out of course.

Function tagfix(ss As String) As String
 
Dim Counter As Integer
Dim s As String
 
tagfix = ""
 
For Counter = 1 To Len(ss)
   
    s = Mid(ss, Counter, 1)
   
    Select Case s
        ' Comment out the following line
        ' if you don't want spaces removed
        Case " "
          ' skip spaces
          ' do nothing
        Case 0 To 9
          tagfix = tagfix & s
        Case "a" To "z"
          tagfix = tagfix & UCase(s)
        Case "A" To "Z"
          tagfix = tagfix & s
        Case Else
          tagfix = tagfix & "-"
        End Select
 
Next

Wait there’s more! If you setup the following subroutine you can run the fix on a selection.

Sub fixtag()
 
Dim c As Range
 
For Each c In Selection
    c = tagfix(c.Value)
Next c
 
End Sub

Leave a Reply