Hi Don,

I think the following is a better approach:
Sub KillPhones()
Dim oCel As Range
Dim strVal As String
Dim i As Integer
With ActiveSheet.UsedRange
For Each oCel In .Columns(2).Cells
If InStr(oCel.Value, Chr(10)) 0 Then
strVal = Split(oCel.Value, Chr(10))(UBound(Split(oCel.Value, Chr(10))))
i = Len(strVal)
strVal = Replace(Replace(Replace(Replace(strVal, "-", ""), " ", ""), "(", ""), ")", "")
i = i - Len(strVal) + 1
If i = 3 Then _
If IsNumeric(strVal) Then oCel.Value = Left(oCel.Value, Len(oCel.Value) - Len(strVal) - i)
End If
Next
End With
End Sub

With this code, pretty well any phone number format comprising the last line will be stripped out, but zip codes in any format will
be left intact.

--
Cheers
macropod
[Microsoft MVP - Word]


"Don Guillett" wrote in message ...
I strongly advise AGAINST posting in more than one group and every couple of minutes. Bat netiquette. However, this should get you
started IF? you have a 5 number zip code. It finds the 1st number after an arbitrary number of characters in the string and then
strips out the rest after 5 numbers.

Sub findnumberintext()
For Each c In Selection
For i = 6 To Len(c)
If IsNumeric(Mid(c, i, 1)) Then
ml = i
Exit For
End If
Next i
MsgBox Left(c, ml + 5)
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message ...
I have a spreadsheet in excel 2003. In cell a1 I have a name for
example:Smith,Lisa
In cell B2 I have an address and a phone number
for example
1234 Anywhere st
Akron,Ohio 44332
330-555-1212

I need to eliminate the phone number from the second field in order to use
it as the basis for a mail merge
In other words I only want the street, city and state left without the phone
number

I can't figure out how to do that successfully. The street
then the city state and zip
and finally the phone are on different lines in the same field

Can you put me in the right direction? Thanks so much