Pull out lastname

  • Thread starter Thread starter WLMPilot
  • Start date Start date
W

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
 
WLMPilot said:
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
Click to expand...
 
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 Rothstein said:
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)
Click to expand...


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.
 
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.
 
Back
Top