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 : https://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 : https://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: