Blog

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

I had written my own but it had a bug. Then I found this code below on this site.
It gives credit to John Walkenbach who writes, bar none, the best books on Excel.

Public Function ExtractElement(Txt, n, Separator) As String
' Returns the nth element of a text string, where the
' elements are separated by a specified separator character
Dim Txt1 As String, temperament As String
Dim ElementCount As Integer, i As Integer
Txt1 = Txt
' If space separator, remove excess spaces
If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator Then _
Txt1 = Txt1 & Separator
' Initialize
ElementCount = 0
TempElement = ""
' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
If ElementCount = n Then
' Found it, so exit
ExtractElement = TempElement
Exit Function
Else
TempElement = ""
End If
Else
TempElement = TempElement & Mid(Txt1, i, 1)
End If
Next i
ExtractElement = ""
End Function

And the following function returns nothing more than the number of elements you get when you cut a string according to a particular delimiter.

Public Function ExtractElementCount(Txt, Separator) As String
' Returns the number of different elements in a string
' separated by a specified separator character
' This is useful if you are, for example trying to grab the last element or the second to last.
Dim Txt1 As String, temperament As String
Dim ElementCount As Integer, i As Integer
Txt1 = Txt
' If space separator, remove excess spaces
If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator Then _
Txt1 = Txt1 & Separator
' Initialize
ElementCount = 0
TempElement = ""
' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
End If
Next i
ExtractElementCount = ElementCount
End Function

Footnote

I see that John Walkenbach has a post about The Versatile Split Function

VBA’s Split function, introduced with Excel 2000, can simplify many programming tasks. This function accepts a text string, and returns a zero-based variant array that contains the elements of the string (you specify the character that delimits the elements).

And then you can replace the ExtractElement with just this! Wow!

Function ExtractElement(str As String, n As Integer, sepChar As String) As Variant
' Returns the nth element from a string,
' using a specified separator character
Dim x As Variant
x = Split(str, sepChar)
If n > 0 And n - 1 < = UBound(x) Then
ExtractElement = x(n - 1)
Else
ExtractElement = ""
End If
End Function

And in case you haven’t had enough you can just do a word count with this.

Function WordCount(txt as String) As Long
' Returns the number of words in a string
Dim x As Variant
txt = Application.Trim(txt)
x = Split(txt, " ")
WordCount = UBound(x) + 1
End Function

There are some more examples on the j-walk site.

• extract a path or a filename from a full filespec
• Counting specific characters in a string
• Finding the longest word