Just a thought.
Split is a pretty expensive operation timewise. You might want to
Dim v as Variant
v = Split(cell.Offset(0, -4)," ")

then work with V.

It won't look as "Kool", but will be a lot faster.

--
Regards,
Tom Ogilvy



"Toppers" wrote in message
...
Based on your original posting, and testing if middle name exists:


AEmployeeLastName = (Split(cell.Offset(0, -4), "
")(UBound(Split(cell.Offset(0, -4), " "))))
AEmployeeFirstName = (Split(cell.Offset(0, -4), "
")(LBound(Split(cell.Offset(0, -4), " "))))
If UBound(Split(cell.Offset(0, -4), " ")) = 2 Then ' contains (at least)
one middle name
AEmployeeInitial = Left((Split(cell.Offset(0, -4), " ")(1)), 1)
Else
AEmployeeInitial = " "
End If

MsgBox AEmployeeFirstName & " " & AEmployeeInitial & " " &
AEmployeeLastName


"JMB" wrote:

If all of your data is in the format of your two examples, one way could
be:

Sub Test()
Dim x As String
x = Left(ActiveCell.Text, _
InStr(1, ActiveCell.Text, " ", vbTextCompare) + 1)
MsgBox x
End Sub

This just evaluates the active cell, so change as needed.

"mikeburg" wrote:


I've been using the following to extract a last name from a list of
individuals:

AEmployeeLastName = Split(Cell.Offset(0, -4), "
")(UBound(Split(Cell.Offset(0, -4), " ")))

What would be the simplest VBA code to extract the 1st name & middle
initial?

For example

John A Doe
Extract John A

or

Jack Franklin Johnson
Extract Jack F

I really appreciate all your help. mikeburg


--
mikeburg

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