Extract last name from Last, First
 

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!

Extract last name from Last, First
 

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




"laavista" wrote in message
...
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!

Extract last name from Last, First
 

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" wrote in message
...
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!

Extract last name from Last, First
 

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" wrote:

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!

Extract last name from Last, First
 

THANK YOU!!!


"FSt1" wrote:

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" wrote:

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!

Extract last name from Last, First
 

This is great. Thanks so much!

"Rick Rothstein" wrote:

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" wrote in message
...
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!

.

Extract last name from Last, First
 

Thanks for responding. This helps!

"JLGWhiz" wrote:

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




"laavista" wrote in message
...
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!


.

Extract last name from Last, First
 

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...
--
PeteCresswell