Sub Extract_Names_1() Dim myName As String, name As String, Surname As String myName = "Ian David Huitson" name = Left(myName, InStr(1, myName, " ") - 1) ' = "Ian" Surname = Right(myName, Len(myName) - InStrRev(myName, " ", -1)) ' = "Huitson" End Sub Sub Extract_Names_2() Dim myName As String, FirstName As String, Surname As String Dim arr() As String Dim name As Variant myName = "Ian David Huitson" arr = Split(myName, " ") 'Split the name using a space character For Each name In arr Debug.Print name Next 'Access the first name (0 = First element of the array) FirstName = arr(0) ' = "Ian" 'Access the Surname (Last element of the array) Surname = arr(UBound(arr, 1)) ' = "Huitson" End Sub