Replacing text in a cell with blanks



I have a spreadsheet in excel 2003. In cell a1 I have a name for
In cell B2 I have an address and a phone number
for example
1234 Anywhere st
Akron,Ohio 44332

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

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




Hi Tom,

Are all the phone numbers entered in the same format with the hyphens? If
they are then you can use Find and Replace.

NOTE: Backup you workbook before doing this in case it returns unexpected

Select the column of data.
Select Replace
In the Find field enter ???-???-????
Leave the replace field blank.
Click Replace all


Hi again Tom,

The following might be a better answer because it will also take the line
feed out before the phone number.

In the Find what field enter the following
Click in the field before the the first question mark.
Hold the Alt key down and using the numeric key pad key in 0010.
The question marks will look like they have gone and replaced with a
flashing dot. Don't worry, the question marks are on the next line and not
Leave the Replace field blank.
Replace all.

NOTE: You must use the Numeric key pad with the Alt key. Using the numerics
above the alpha characters do not work.


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