Select Page

I had written my own but it had a bug. Then I found this code below on this site.

```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

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