Extract last name into a variable using VBA code

[mikeburg]

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

 

[Norman Jones]

Hi Mike,

For xl2k+, try:

'=============>>
Public Sub Tester001()
Dim sStr As String
Dim Pos As Long

sStr = Range("A1").Value

Pos = InStrRev(sStr, " ")

sStr = Mid(sStr, Pos + 1)

MsgBox sStr

End Sub
'<<=============

 

[Ron Rosenfeld]

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

 

[mikeburg]

Wonderful!

How would you extract the first name using the simplest VBA code?

Thanks, mikeburg

 

[Gary Keramidas]

here's one way if the name is in a1


dim fname as string
sub fname()
Range("a2").Value = Left(Range("a1"), InStr(1, Range("a1"), " ") - 1)
end sub

 

[Tom Ogilvy]

Sub foo()
Dim LastName As String
Dim Temp

Temp = Split(ActiveCell)

LastName = Temp(UBound(Temp))
FirstName = Temp(LBound(Temp))

Debug.Print LastName, FirstName

End Sub

 

[David]

Tom Ogilvy wrote

Debug.Print LastName, FirstName

I've often seen this syntax for testing. Where do the results actually show
up? I ran the sub from one of my workbooks and didn't see any result.

 

[Tom Ogilvy]

In the VBE, go to view and select immediate window.

That is where they show up. Also, like the name says, it is a good place to
immediately execute instructions. For example. go to the immediate window
and enter

Range("B9:F10").Interior.ColorIndex = 3 <cr>

and the active sheet should be colored as indicated.

Suppose you were constructing a complex string and wanted to test it.

v = 2 <cr>
y = 6 <cr>
? y & "/" & v & " = " & y/v <cr>

The question mark is equivalent to debug.print, so it evaluates the
expression and displays the results.

Very useful.

 

[Ron Rosenfeld]

Wonderful!

How would you extract the first name using the simplest VBA code?

Thanks, mikeburg

Basically the same macro:

VBA6+

======================
Option Explicit

Sub foo()
Dim LastName As String, FirstName as String
Dim Temp

Temp = Split(ActiveCell)

LastName = Temp(UBound(Temp))
FirstName = Temp(LBound(Temp))

Debug.Print FirstName
Debug.Print LastName

End Sub
======================

Pre VBA6 you would just need to find the first space, and then use the Left
function.


--ron

 

[David]

Tom Ogilvy wrote

In the VBE, go to view and select immediate window.

That is where they show up. Also, like the name says, it is a good
place to immediately execute instructions. For example. go to the
immediate window and enter

Range("B9:F10").Interior.ColorIndex = 3 <cr>

and the active sheet should be colored as indicated.

Suppose you were constructing a complex string and wanted to test it.

v = 2 <cr>
y = 6 <cr>
? y & "/" & v & " = " & y/v <cr>

The question mark is equivalent to debug.print, so it evaluates the
expression and displays the results.

Very useful.

Ah, yes. Thank you. I've often used the immediate window to test results
with ?, but didn't know debug.print was its equivalent in code - until now.