Prev: Run a macro upon saving
Next: Force increased width of GetOpenFilename dialogue box?
From: WLMPilot on 12 May 2010 16:02
Cell A1 = "John Doe" (without quotes)

In a macro, I have the following:
name = Range("A1").Value

How to I pull out just the last name of the person in the variable name and
place it in the variable, lastname?

Thanks,
Les
From: Brian on 12 May 2010 16:34
Formula wise, =RIGHT(A1,(FIND(" ",A1)-2)). You can work that into a macro if
need be.

"WLMPilot" wrote:

> Cell A1 = "John Doe" (without quotes)
>
> In a macro, I have the following:
> name = Range("A1").Value
>
> How to I pull out just the last name of the person in the variable name and
> place it in the variable, lastname?
>
> Thanks,
> Les
From: Brian on 12 May 2010 16:34


"WLMPilot" wrote:

> Cell A1 = "John Doe" (without quotes)
>
> In a macro, I have the following:
> name = Range("A1").Value
>
> How to I pull out just the last name of the person in the variable name and
> place it in the variable, lastname?
>
> Thanks,
> Les
From: Rick Rothstein on 12 May 2010 16:37
There is no fool-proof way of parsing a field where the first and last names
exist together. There are first names composed of two names (such as Mary
Anne) and last names composed of two name parts (such as Della Rossa, who is
a friend of mine; or da Vinci of Leonardo fame; or de Fermat of mathematical
fame). I can get you the last separated name (whether that is the whole last
name or not is for you to decide)...

LastName = Mid(name, InStrRev(name, " ") + 1)

--
Rick (MVP - Excel)



"WLMPilot" <WLMPilot(a)discussions.microsoft.com> wrote in message
news:3D1390E2-438D-4C4C-B3EB-04E08CAC6A70(a)microsoft.com...
> Cell A1 = "John Doe" (without quotes)
>
> In a macro, I have the following:
> name = Range("A1").Value
>
> How to I pull out just the last name of the person in the variable name
> and
> place it in the variable, lastname?
>
> Thanks,
> Les

From: Helmut Meukel on 12 May 2010 17:51
"Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> schrieb im Newsbeitrag
news:uOVKkLh8KHA.4648(a)TK2MSFTNGP04.phx.gbl...
> There is no fool-proof way of parsing a field where the first and last names
> exist together. There are first names composed of two names (such as Mary
> Anne) and last names composed of two name parts (such as Della Rossa, who is a
> friend of mine; or da Vinci of Leonardo fame; or de Fermat of mathematical
> fame). I can get you the last separated name (whether that is the whole last
> name or not is for you to decide)...
>
> LastName = Mid(name, InStrRev(name, " ") + 1)
>
> --
> Rick (MVP - Excel)
>


Just to add some more examples:
Lois McMaster Bujold
William H. Keith Jr.
A. E. Van Vogt
Lillian Steward Carl
all the above are authors.
BTW, Lois was born as Lois McMaster
and Lillian as Lillian Steward.

Another example: DuPont's full name is
E. I. du Pont de Nemours and Company
named after it's founder Eleuthère Irénée du Pont,
a french immigrant who came from Nemours in
France. (Don't know if it refers to the city or the
duchy).

Helmut.

 |  Next  |  Last
Pages: 1 2
Prev: Run a macro upon saving
Next: Force increased width of GetOpenFilename dialogue box?