Extract 1st name & middle initial

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,

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" <mikeburg.22uivm_1139293501.4105@excelforum-nospam.com> wrote in
message news:mikeburg.22uivm_1139293501.4105@excelforum-nospam.com...
>
> 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

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" <Toppers@discussions.microsoft.com> wrote in message
news:BA3F1115-25D3-462B-9111-FAD6AE6434A9@microsoft.com...
> 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
> > >
> > >