Pull out lastname

[WLMPilot]

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

 

[Brian]

Formula wise, =RIGHT(A1,(FIND(" ",A1)-2)). You can work that into a macro if
need be.

 

[Brian]

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

 

[Rick Rothstein]

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)

 

[Helmut Meukel]

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)

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.

 

[JLGWhiz]

You can use this method to return the last name, no matter how many first
and middle names are listed. This returns the last text separated by a
space.

Sub dj()
Dim lnm
lnm = Split(Range("B2"), " ", -1)
Range("B4") = lnm(UBound(lnm))
End Sub

If the name is in B2 it will put the last name in B4. You can work this
into a loop for a range of names.