finding and replacing text with a blank

[Tom]

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

 

[FSt1]

hi
not sure but this might help out.
with address/phone in b2...
in b4 enter......=RIGHT(B2,12)
this will extract the phone number to b4 and assumes that the Phone number
is 7 digits with 3 digit area code and dashes between area code and phone
number.
in b3 enter......=substitute(B2,B4,"",1)
or in cell B4 substiture the text in b4 with nothing one time.

copy and paste special values the formulas in b3 and b4. delete B2.

personally i would put the formulas in column C and D so that it would be
easier to copy the fomulas down as far as you need because i suspect you
might have a long list.

regards
FSt1

 

[Don Guillett]

See ans in post in other ng and please post in ONLY one.