Get The Nth Word From A Text String with vba

less than 1 minute read

An easy user defined function (UDF) to get the nth word from cell. I often use this to get first and last names when the full name is in a single cell.

'==========================================================================================================
' ## Function: Get the nth word from a text string
'==========================================================================================================
Function ExtractNthWord(Source As String, Position As Integer, Delimiter As String) As String
    Dim arr() As String
    Dim lCount As Long
    
    arr = VBA.Split(Source, Delimiter)
    lCount = UBound(arr)
    
    If lCount < 1 Or (Position - 1) > lCount Or Position < 0 Then
        ExtractNthWord = ""
    Else
        ExtractNthWord = arr(Position - 1)
    End If
    
End Function