Advertisements

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
Categories: User Defined FunctionTags: , ,

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: