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


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=503728

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
'<<=============


---
Regards,
Norman



"mikeburg" wrote in
message ...

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


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=503728

Wonderful!

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

Thanks, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=503728

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


Gary


"mikeburg" wrote in
message ...

Wonderful!

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

Thanks, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=503728

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

--
Regards,
Tom Ogilvy


"mikeburg" wrote in
message ...

Wonderful!

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

Thanks, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=503728

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.

--
David

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.

--
Regards,
Tom Ogilvy



"David" wrote in message
...
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.

--
David

On Sun, 22 Jan 2006 01:08:26 -0600, mikeburg
wrote:


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

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.

--
David