I've been using the following to extract a last name from a list o
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 & middl
initial?

For example

John A Doe
Extract John A

or

Jack Franklin Johnson
Extract Jack F

I really appreciate all your help. mikebur

--
mikebur
-----------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...fo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=50924

mikeburg,

Public Function GetFirstAndInitial(argIn As Range) As String
Dim TempVar As Variant
'Add error checking to make sure .Range is single value, etc
TempVar = Split(argIn.Value, " ")
'Add error checking to make sure 0 & 1 are valid array elements. etc
GetFirstAndInitial = TempVar(0) & " " & Left(TempVar(1), 1)
End Function

NickHK

"mikeburg" wrote in
message ...

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

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

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

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

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