| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I do this with VBA?
For instance, I have " Mr. Jack Adam" and I want to trim the first name and have "Mr. Adam" left. How can I do that? Thanks a lot. slamm -- slamm ------------------------------------------------------------------------ slamm's Profile: http://www.excelforum.com/member.php...o&userid=15018 View this thread: http://www.excelforum.com/showthread...hreadid=483829 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I do this with VBA?
Hi,
Courtesy of John Walkenbach Function NOMIDDLE(n) As String ' NOTE: The InStrRev function is not available in Excel 97 Dim FirstName As String, LastName As String n = Application.WorksheetFunction.Trim(n) FirstName = Left(n, InStr(1, n, " ")) LastName = Right(n, Len(n) - InStrRev(n, " ")) NOMIDDLE = FirstName & LastName End Function A1 contains "Mr. Jack Adam", B1 contains =NOMiddle(A1)) to give "Mr. Adam". "Mr. JacK James Adam" will return "Mr. Adam" HTH "slamm" wrote: For instance, I have " Mr. Jack Adam" and I want to trim the first name and have "Mr. Adam" left. How can I do that? Thanks a lot. slamm -- slamm ------------------------------------------------------------------------ slamm's Profile: http://www.excelforum.com/member.php...o&userid=15018 View this thread: http://www.excelforum.com/showthread...hreadid=483829 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I do this with VBA?
Thanks a lot. I do appreciate it. -- slamm ------------------------------------------------------------------------ slamm's Profile: http://www.excelforum.com/member.php...o&userid=15018 View this thread: http://www.excelforum.com/showthread...hreadid=483829 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I do this with VBA?
Assume name is in cell A1 then .........
xname = Trim(Range("A1")) MsgBox Left(xname, InStr(1, xname, " ")) & Right(xname, InStrRev(xname, " ") - 2) This assumes there is at least one space between the salutation, first and last name. If the name is formatted without a space after the salutation, e.g. Mr.Jack Adams, then use.......... MsgBox Left(xname, InStr(1, xname, ".")) & Right(xname, InStrRev(xname, " ") - 1) -- Cheers Nigel "slamm" wrote in message ... For instance, I have " Mr. Jack Adam" and I want to trim the first name and have "Mr. Adam" left. How can I do that? Thanks a lot. slamm -- slamm ------------------------------------------------------------------------ slamm's Profile: http://www.excelforum.com/member.php...o&userid=15018 View this thread: http://www.excelforum.com/showthread...hreadid=483829 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
