Extract the Nth word in a String in Excel

Get Nth Word Excel Example
Get Nth Word Excel Example

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:

Get Nth Word Excel Example
Get Nth Word Excel Example