Example If cell A1= STEPHEN R WILSON
I would like A1=STEPHEN WILSON

=LEFT(A1,FIND(" ",A1))&MID(A1,FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Shani" wrote in message
oups.com...
Example If cell A1= STEPHEN R WILSON
I would like A1=STEPHEN WILSON

more examples would help - are they all first name initial last name,
or do we have some with more initials, no initials etc?

Try some code like


Dim Pos1 As Long
Dim Pos2 As Long
Pos1 = InStr(1, Range("A1").Text, " ")
If Pos1 = 0 Then
Exit Sub
End If
Pos2 = InStr(Pos1 + 1, Range("A1").Text, " ")
If Pos2 = 0 Then
Exit Sub
End If

Range("A1").Value = Left(Range("A1").Text, Pos1 - 1) & " " _
& Mid(Range("A1").Text, Pos2 + 1)





--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Shani" wrote in message
oups.com...
Example If cell A1= STEPHEN R WILSON
I would like A1=STEPHEN WILSON

Sub BBB()
Dim s As String, iloc As Long
Dim iloc1 As Long
s = Range("A1").Value
iloc = InStr(1, s, " ", vbTextCompare)
iloc1 = InStr(iloc + 1, s, " ", vbTextCompare)
Range("A1") = Left(s, iloc) & Right(s, Len(s) - iloc1)

End Sub

--
Regards,
Tom Ogilvy


"Shani" wrote:

Example If cell A1= STEPHEN R WILSON
I would like A1=STEPHEN WILSON

Thank you all