Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull out lastname
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull out lastname
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull out lastname
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull out lastname
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull out lastname
"Rick Rothstein" schrieb im Newsbeitrag
... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull out lastname
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. "WLMPilot" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separate lastname from firstname | Excel Worksheet Functions | |||
lastname, first name | Excel Worksheet Functions | |||
LastName, FirstName covert to FirstName LastName | Excel Discussion (Misc queries) | |||
How to change lastname, firstname to firstname lastname in Excel? | Excel Discussion (Misc queries) | |||
How to seperate lastname and firstname? | Excel Programming |