Extract last name into a variable using VBA code

  • Thread starter Thread starter mikeburg
  • Start date Start date
M

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
 
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
'<<=============
 
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
Click to expand...

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
 
Wonderful!

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

Thanks, mikeburg
 
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
 
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
 
Tom Ogilvy wrote
Debug.Print LastName, FirstName
Click to expand...

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.
 
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.
 
Wonderful!

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

Thanks, mikeburg
Click to expand...

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
 
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.
Click to expand...

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.
 
Back
Top