Extract last name from Last, First

  • Thread starter Thread starter laavista
  • Start date Start date
L

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!
 
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
 
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.
 
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
 
THANK YOU!!!


FSt1 said:
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
Click to expand...
 
This is great. Thanks so much!

Rick Rothstein said:
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)





.
Click to expand...
 
Thanks for responding. This helps!

JLGWhiz said:
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







.
Click to expand...
 
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
Click to expand...

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