Exported phone numbers can't be changed (1 Viewer)

[Divit11]

Hi All,

I'm still using Access and Excel 2000. I store phone numbers in my Access DB without "()" or "-" and use the input mask "000\-000\-0000;1;_". Everything was fine until I was asked to export this information into an Excel spreadsheet. In Excel all my phone numbers were displayed bare, without any separators as expected. I tried selecting the top of the column and clicked the Format>Cells>Number Tab>Special and selected Phone Number in the drop down box then OK. Nothing happened to the existing numbers. If I change a number or add a new number at the bottom of that column then I get (630) 555-1212 (as an example).

What am I missing? I really don't want to change my data base format to correct this occasional display problem in Excel.

D

 

[boblarson]

Why not use a query to format the phone numbers first and export the query to Excel so that the numbers get exported correctly to begin with.

 

[Divit11]

Problem solved.

Thanks Bob for your reply. I did a search on "SQL" and "Format" and found an example used to convert dates. The scary part was just to substitute my phone mask and it worked the first time.

For those that read this far my query solution included the following:

Format (tblMemberListings.mlHomePhone, "000-000-0000") AS HomePhone,

D

 

[shades]

Also, in Excel, they are not necessarily being considered as numbers, rather text. You can set text format achieve what you need.
________
HERBAL VAPORIZERS

 

[Divit11]

Hi Shades,

Am I missing something in MS Excel 2000? When I export the queried results to Excel the values displayed have the ' prefix when any cell is selected and the value is displayed in the formula bar window (ie. '1234567890).

Under the number tab of the Format Cell menu the only place I can create a new format is by clicking the Custom option. As I found out later if I change any value in that column it will get redisplayed with (000) 000-0000 format, but I must change each cell value which is too time consuming to be worthwhile.

Maybe this was a "fix" in one of the later versions. Maybe someone can confirm that.

D

 

[shades]

As for the ' in front of the numbers, that is something that comes from the database, meaning it is not considered a number, but text, which in this case is good.

I don't have XL 2000 (went from 97 to 2002 to 2003). Do you have the format painter? If so, then you can click the cell that has proper formatting, then click the painter button and select all the other cells you want with the same formatting.

If the cells are non-continguous, then double-click the painter button. Be sure to click the painter button at the end of your work, so that it dselects the painter button.

If you don't have the Format Painter option, then go to the cell and use the Format Cells option to get what you want, then (don't do anything else) select the other cells and click the F4 button, which repeats last action. And you can keep on using that for as many cell sections as you have.
________
TS100

 

[Divit11]

Thanks, Shades, for persisting. I had to install the Format Painter in my standard tool bar and then tried to follow your instructions but no joy. I dug out one of my Excel manuals and tried to follow their instructions, still no joy.

I'll sit down with one of my Excel fluent associates and get them to teach this old dog that new trick. Meanwhile my SQL Format As code is clean and quicker since I can just pass the Excel file along without any special modifications.

D