Extract 1st name & middle initial

[mikeburg]

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

 

[NickHK]

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

 

[Guest]

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.

 

[Guest]

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

 

[mikeburg]

Wonderful! Wonderful! Thanks for all your help. mikeburg

 

[Tom Ogilvy]

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.