Excel Parser

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, fex, 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

Leave a Reply