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




"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!

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!

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!

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!

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!

.

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!


.

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