Change 1234567890 to 123-456-7890
 

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?

Change 1234567890 to 123-456-7890
 

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

Rick


"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

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 editpaste special and select add


Now try it



--


Regards,


Peo Sjoblom



"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

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


Replace @mailinator.com with @tiscali.co.uk


"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Describe "no luck"... what does happen (that is, what do you see in the
cell)?

Rick


"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

Doh! I bet that was it


--


Regards,


Peo Sjoblom


"Rick Rothstein (MVP - VB)" wrote in
message ...
Just out of curiosity, are you seeing a bunch of # symbols in the cell? Is
so, try widening the column.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Describe "no luck"... what does happen (that is, what do you see in the
cell)?

Rick


"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

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.

Change 1234567890 to 123-456-7890
 

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

"Rick Rothstein (MVP - VB)" wrote:

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

Rick


"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

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.

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

That did it. Thank you so much.

"Peo Sjoblom" wrote:

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 editpaste special and select add


Now try it



--


Regards,


Peo Sjoblom



"ann" wrote in message
...
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?

Change 1234567890 to 123-456-7890
 

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


Replace @mailinator.com with @tiscali.co.uk


"ann" wrote in message
...
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.

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"ann" wrote in message
...
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?