Extract last name from Last, First

[laavista]

I know this has to be a common question, but I can't find the answer...

Using VBA, I need to extract the last name from the format:
Last, First

For example:
the cell contents extract to a variable the value of
JONES, AMY JONES
ADAMS, HARRY ADAMS

Your help would really be appreciated!

 

[JLGWhiz]

I don't know how you are using it, but this shows how to get it in a
variable.

Sub dk()
Dim lName As String
lName = Left(ActiveCell, InStr(ActiveCell, ",") - 1)
MsgBox lName
End Sub

 

[Rick Rothstein]

There are a couple of ways you can do this. Assuming this assignment....

CellName = Range("A1").Value

you can do either this...

LastName = Split(CellName, ",")(0)

or this...

LastName = Left(CellName, InStr(CellName, ",") - 1)

your choice.

 

[FSt1]

hi
Sub CheckName()
Dim r As Range
Dim s As String
Set r = Range("A5")
s = Left(r, WorksheetFunction.Find(",", r) - 1)
MsgBox s
End Sub

regards
FSt1

 

[laavista]

THANK YOU!!!

hi
Sub CheckName()
Dim r As Range
Dim s As String
Set r = Range("A5")
s = Left(r, WorksheetFunction.Find(",", r) - 1)
MsgBox s
End Sub

regards
FSt1

 

[laavista]

This is great. Thanks so much!

There are a couple of ways you can do this. Assuming this assignment....

CellName = Range("A1").Value

you can do either this...

LastName = Split(CellName, ",")(0)

or this...

LastName = Left(CellName, InStr(CellName, ",") - 1)

your choice.

--
Rick (MVP - Excel)





.

 

[laavista]

Thanks for responding. This helps!

I don't know how you are using it, but this shows how to get it in a
variable.

Sub dk()
Dim lName As String
lName = Left(ActiveCell, InStr(ActiveCell, ",") - 1)
MsgBox lName
End Sub







.

 

[(PeteCresswell)]

Per laavista:

Using VBA, I need to extract the last name from the format:
Last, First

For example:
the cell contents extract to a variable the value of
JONES, AMY JONES
ADAMS, HARRY ADAMS

Feasibility depends on your data and how often you need to do it

If it's just a few mostly straight-up names, others' solutions
apply.... and you can manually correct the exceptions.

If there are a *lot* of names and/or you have to do it often be
advised that the Mc's, the Mac's, the Di's the D's, the II's, the
III's, the IV's, the Phd's and a whole host of others will drive
you nuts - not to mention people with on-letter last names...

Lookup tables will be involved...