# 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)) &lt;&gt; 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 &amp; 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)) &lt;&gt; Separator Then _
Txt1 = Txt1 &amp; 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 &gt; 0 And n - 1 &lt; = 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