Extract 1st name & middle initial

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

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