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

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

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

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