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

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
```
• extract a path or a filename from a full filespec
• Counting specific characters in a string
• Finding the longest word

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