Excel 2010
I have a column of 22,000 phone numbers (and an additional column of fax numbers).
I'm trying to convert the numbers from nine digits to nine digits with hyphens.
I copied and renamed the original document. There are no protections.
To be certain, I ran the Doc Inspector. I'm still free to do anything to the list.
The instructions are easy:
Highlight column, select Format cell, click Custom, in Type, insert XXX-XXX-XXXX, click OK. That didn't work.
Next, instead of click Custom, I clicked Special, selected phone number, clicked OK.
Instead of hyphens, if it adds parentheses around the area code. I'm not that picky, although it takes more space.
That doesn't work either.
I am less than a novice in Excel when it comes to creating anything. I've managed to fill in others and not have to create my own.
The only thing that doesn't look like instructions is that the Sample above Type never changes.
I don't know if it should.
Thank you.
Highlight column, select Format cell, click Custom, in Type, insert XXX-XXX-XXXX, click OK. That didn't work
Excel 2003: Try: ####0-##0-###0
Be sure to start with number format.
PiKo,
I have Excel 2010, but I am sure that doesn't make an ounce of difference.
Your suggestion does not work, either.
There is no other option but formatting Number. Since you mentioned it, though, I looked at Protection. The default is "locked" so I unchecked it - just to test.
I would think it's something simple, but can't guess.
Thank you for your quick response.
I did some experiments, but custom formatting in "Type" has always an immediate effect on "Sample" (and the cells).
Also with me, Protection is Locked, but that has no influence.
I suppose you tried this also on one cell instead of 22000 cells?
And you tried it also in a new document?
When it does not work in a new document either, maybe a repair action of Office is required.
When it does work in a new document, the problem should be related to something in the telephone document.
In the latter case, you may export all data and import these into a new document.
I don't have any further ideas.
Success!
In Excel 2010 when you select Custom in format cells, Type In ###-###-####where it says type. This will create a custom format.
PiKo,
Logic prevailed for the first three. Starting at the fourth:
No, of course, I didn't try it in a different doc, until a moment ago.
As you said, the sample immediately changed.
It worked like a charm.
Having done this, I am certain your export/import suggestion will as well.
With certain programs and in certain situations, I know where the blame lies.
As an avid Excel avoider, I hadn't a clue.
Success - you nailed it!
Thank you.
pbcopter,
See post - #1 - I tried that and failed.
It wasn't the formula, per se; that particular column or the doc itself is somehow flawed or corrupted.
PiKo had me try it in another sheet, so I did.
It took a second to prove his/her theory.
Nothing succeeds like success!
Thank you.
