Change 1234567890 to 123-456-7890

[ann]

I am trying to do the opposite for what everyone else has been asking for
help in resolving a phone number issue.

I want to key in each cell nine digits and have it automatically format into
a phone number format as shown in my subject. I have gone to
Format/Cells/Number to select the Special Type for Phone numbers, no luck.
The won't change. Then I thought I would create a custom format of both
###-###-#### and (###)###-####, once again no luck. I have tried everything
short of pulling out my hair. Does any one have any suggestions?

 

[Rick Rothstein \(MVP - VB\)]

Describe "no luck"... what does happen (that is, what do you see in the
cell)?

Rick

 

[Peo Sjoblom]

I assume you meant 10 digits?

Select an empty cell, do a custom format of

<=9999999]###-####;(###) ###-####

copy the cell, select the range where you want these
phone numbers and do edit>paste special and select add


Now try it



--


Regards,


Peo Sjoblom

 

[Sandy Mann]

Your Custom forma works for me. Is the cell *number* actually text? Try
testing with =ISTEXT(K1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

Just out of curiosity, are you seeing a bunch of # symbols in the cell? Is
so, try widening the column.

Rick

 

[Peo Sjoblom]

Doh! I bet that was it


--


Regards,


Peo Sjoblom

 

[Dave]

Hi,
Pulling out your hair is an over-rated experience. You have done well to
resist.

I am using XL2000. The custom format ###-###-#### works for me. What results
does it give you?
Regards - Dave.

 

[ann]

The format of the cell does not change. It appears the same and the size of
the column is large enough.

 

[ann]

I used your formula and determined it is indeed text. Using Format/Cells I
changed it to a number, hit ok, re-opened Format/Cells and tried to change it
to a ###-###-#### format. Nothing changed in the cell.

 

[ann]

That did it. Thank you so much.

I assume you meant 10 digits?

Select an empty cell, do a custom format of

<=9999999]###-####;(###) ###-####

copy the cell, select the range where you want these
phone numbers and do edit>paste special and select add


Now try it



--


Regards,


Peo Sjoblom

I am trying to do the opposite for what everyone else has been asking for
help in resolving a phone number issue.

I want to key in each cell nine digits and have it automatically format
into
a phone number format as shown in my subject. I have gone to
Format/Cells/Number to select the Special Type for Phone numbers, no luck.
The won't change. Then I thought I would create a custom format of both
###-###-#### and (###)###-####, once again no luck. I have tried
everything
short of pulling out my hair. Does any one have any suggestions?

 

[Sandy Mann]

That is because changing the format does not chnge what is in the cell so it
just remained as text. Peo's solution worked because it forced the contents
of the cell to change to a number.

But I'm glad that you got it working, that is the important thing.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk