Visual Basics for Applications (VBA) allows users to build on the existing functionality of Microsoft Excel. It comes with a strong set of inbuilt functions that are often not readily accessible from Excel’s interface. These functions have to be wrapped inside a user defined function to be usable in Excel. A classic example is the Split function.
The Split function separates a string into sub strings based on a separator/delimiter, and stores each sub string in an Array element. The lower bound of this array is ‘0’. Consequently, the Nth word could be accessed by extracting the N-1th element in the array.
'================================================================================
'Function to extract the Nth word in a String
'with an optional seperator character
'Author : Ejaz Ahmed
'Date : 22 January 2014
'Website : http://strugglingtoexcel.wordpress.com/
'================================================================================
Function GetNthWord(ByVal WhichText As String, _
ByVal WhichWord As Long, _
Optional ByVal Seperator As String = " ") As Variant
'Remove any trailing spaces
WhichText = Trim(WhichText)
'Store the Answer in a temporary string
Dim StrAns As String
'Returning Value error if the Seperator character/string is not found
If InStr(1, WhichText, Seperator) = 0 Then
GetNthWord = CVErr(xlErrValue)
Exit Function
End If
StrAns = Trim(VBA.Split(WhichText, Seperator, , _
vbBinaryCompare)(WhichWord - 1))
If IsNumeric(StrAns) Then
GetNthWord = CDbl(StrAns)
Else
GetNthWord = StrAns
End If
End Function
The number of words in a string could be obtained by tweaking this function slightly. I have also included a faster running version that I recommend using.
'=========================================================================
'Function to return the number of words in a string;
'with an optional seperator character
'Author : Ejaz Ahmed
'Date : 14 November 2013
'Website : http://strugglingtoexcel.wordpress.com/
'=========================================================================
Function NumberOfWords(ByVal WhichText As String, _
Optional ByVal Seperator As String = " ") As Variant
'Remove any trailing spaces
WhichText = Trim(WhichText)
'Returning Value error if the Seperator character/string is not found
If InStr(1, WhichText, Seperator) = 0 Then
NumberOfWords = CVErr(xlErrValue)
Exit Function
End If
NumberOfWords = CLng(UBound(VBA.Split(WhichText, Seperator, _
, vbBinaryCompare)) + 1)
'Alternate implimentation that runs faster
'NumberOfWords = (Len(WhichText) - _
Len(Replace(WhichText, Seperator, vbNullString, 1, , _
vbBinaryCompare))) / Len(Seperator) + 1
End Function
Below is a screenshot of a spreadsheet illustrating how this function could be used:
