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.
Dim c As Range

    For Each c In Selection
        c = tagfix(c.Value)
        Next c
End Sub

Leave a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.