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