Prev: odbc connection lost
Next: Ribbon Customisation
From: laavista on 20 Mar 2010 14:42
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!
From: JLGWhiz on 20 Mar 2010 14:53
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" <laavista(a)discussions.microsoft.com> wrote in message
news:2694E4E6-6138-480A-8267-B6EA0E53A18E(a)microsoft.com...
>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!


From: Rick Rothstein on 20 Mar 2010 15:01
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" <laavista(a)discussions.microsoft.com> wrote in message
news:2694E4E6-6138-480A-8267-B6EA0E53A18E(a)microsoft.com...
> 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!

From: FSt1 on 20 Mar 2010 15:24
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!
From: laavista on 20 Mar 2010 18:08
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!
 |  Next  |  Last
Pages: 1 2
Prev: odbc connection lost
Next: Ribbon Customisation