Extract the Nth word in a String in Excel


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:

Get Nth Word Excel Example
Get Nth Word Excel Example
Advertisements

One thought on “Extract the Nth word in a String in Excel

  1. Pingback: Excel Worksheet Functions and VBA Functions | Struggling To Excel

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s