finding and replacing text with a blank


T

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
 
Ad

Advertisements

F

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top