Please help with VBA code that extracts the last name from a cell
containing First name, sometimes middle initial or name, & last name
and put into a variable called LastName.
For example in cell A1 is the name John B Doe
Need LastName variable to contain the word Doe.
Thank you so very much. mikeburg
Assuming VBA6 or later:
=======================
Option Explicit
Sub foo()
Dim LastName As String
Dim Temp
Temp = Split(ActiveCell)
LastName = Temp(UBound(Temp))
Debug.Print LastName
End Sub
======================
If you ONLY needed the Last Name, you could also use the InStrRev Function.
But using split allows you to easily pick out the other names.
If you have earlier versions of VBA, these functions are not available. So
something like:
=================================
Option Explicit
Sub foo()
Dim LastName As String
Dim i As Long, NumSpaces As Long
Dim Temp As String
NumSpaces = Len(ActiveCell.Text) - _
Len(Replace(ActiveCell.Text, " ", ""))
Temp = Application.WorksheetFunction. _
Substitute(ActiveCell.Text, " ", Chr(1), NumSpaces)
LastName = Mid(Temp, InStr(1, Temp, Chr(1)) + 1, 255)
Debug.Print LastName
End Sub
=============================
--ron