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 Reply