HELP I need a function or macro to crop cells
 

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

HELP I need a function or macro to crop cells
 

=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

HELP I need a function or macro to crop cells
 

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

HELP I need a function or macro to crop cells
 

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

HELP I need a function or macro to crop cells
 

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

HELP I need a function or macro to crop cells
 

Thank you all