removing hypens to make phone No. for another program

[jkc42]

All my phone numbers are hypenated (555-555-555) I want to upload m
numbers to a fax machine that wants it like this (555555555)
How can I do it? I am using excel 2000.
Your help on this would be greatly appreciated or I'm forced t
re-write thousands of numbers.
Thanks
Jod

 

[David McRitchie]

Hi Jody,
Assuming that they are text and not numbers
=SUBSTITUTE(A1,"-","")

 

[jkc42]

Hi David
Not sure what you mean or how to use your =SUBSTITUTE(A1,"-",""). Bu
if it will work I'm all for it. They are numbers and not text. Al
numbers are phone numbers 555-555-555. Can I remove the hypens t
produce 555555555??
Thanks
Jod

 

[Peo Sjoblom]

If they are numeric they wouldn't have hyphens unless you are using a US
phone format.
Use the formula in a help column and replace A1 with the cell with the first
value, then
copy down/across and copy and paste specials as values, then remove the
original.
If you want the result from the formula to be truly numeric use

=--SUBSTITUTE(A1,"-","")



--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Otto Moehrbach]

Jody
Select all the cells that you want changed. Do Edit - Replace. Put a
"-" (without the quotes) in what to replace and put nothing in what to
replace it with. Click Replace All.
If you want to do this for the whole sheet, then don't select anything.
HTH Otto

 

[David McRitchie]

if they are numbers then change your number format
to be a number they must test as True for the following
=ISNUMBER(A1)

what do you see on the formula bar for such an entry
that have your (555-555-5555)

A1 is cell location of the first cell. Do you see letters across
the top of the spreadsheet.

Now you want 5555555555 instead of (5555555555)
without the parens makes a lot more sense to me.
Do you see parens?

When you click on a cell with such values what do you see
in the formula box, and what do you see in the name box
to the left of the formula bar -- it should usually be an address.


--

 

[jkc42]

Thanks a million Otto
It works.
I'm sorry Peo if I have done something wrong, Not sure what you mean
by your comment "For everyone's benefit keep the discussion in th
newsgroup."
I thought this was where I was to post my question. Please re-direct m
to the proper place for next time.

Thanks again for all your help on this.
Jod

 

[Peo Sjoblom]

It hasn't got anything to do with you, it is my signature, I have changed it
now to avoid more
misunderstanding.

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom