These 2 UDF’s will extract the first and last words using a formula in your worksheet.

It is worth noting that during a data split such as this, the data you are working with may require a Trim() applied to it so any rogue trailing or leading spaces are removed, making the first word and last word more accurate.

Copy the 2 parts below straight into a module in the VB Editor (ALT+F11)

Function GETFIRSTWORD(Text As String, Optional Separator As Variant)
Dim firstword As String
If IsMissing(Separator) Then
Separator = ” ”
End If
firstword = Left(Text, InStr(1, Text, Separator, vbTextCompare))
GETFIRSTWORD = Replace(firstword, Separator, “”)
End Function

Function ReturnLastWord(The_Text As String)
Dim stGotIt As String
stGotIt = StrReverse(The_Text)
stGotIt = Left(stGotIt, InStr(1, stGotIt, ” “, vbTextCompare))
ReturnLastWord = StrReverse(Trim(stGotIt))
End Function