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