Formatting cells after importing from Access

Maria T. Rycyk · Feb 20th, 2002, 03:58 PM

I exported a query from MS Access into MS Excel and now I can not format the numbers. The worksheet is not protected.

Mark W. · Feb 20th, 2002, 04:43 PM

Most likely your numbers were imported as text representations of numbers (e.g., "10", "3", etc.). Number formatting won't work with text. If you can't fix your query to produce numbers you can select your values, and choose the Data | Text to Columns... menu command to convert them. If your query is producing numbers then it may be that the range housing these numbers was initially formatted as Text. Now that you've changed the formatting the process above will respect the new format and convert the values. Happy computing!!

[ This Message was edited by: Mark W. on 2002-02-20 15:48 ]

Anne Troy · Feb 20th, 2002, 04:48 PM

You can also try placing a 1 in any cell (even on another worksheet). Copy that cell. Select the *bad* number cells. Hit Edit-Paste special-Multiply. This multiplies the number by 1, which forces Excel to see those values as numbers.

Feb 20th, 2002, 06:09 PM

Maria,

Use "Dreamboat's" suggestion, but do it in Access. In other words, modify your query to perform the "conversion" when you run your query.

For example, if the offending field is called "Quantity" then modify your query like this:

Qty: Quantity * 1

In this way you don't have to constantly do this conversion in Excel.

enjoy

Bariloche

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 20th, 2002, 03:58 PM	#1
Maria T. Rycyk New Member Join Date: Feb 2002 Posts: 1	I exported a query from MS Access into MS Excel and now I can not format the numbers. The worksheet is not protected.

Feb 20th, 2002, 04:43 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Most likely your numbers were imported as text representations of numbers (e.g., "10", "3", etc.). Number formatting won't work with text. If you can't fix your query to produce numbers you can select your values, and choose the Data \| Text to Columns... menu command to convert them. If your query is producing numbers then it may be that the range housing these numbers was initially formatted as Text. Now that you've changed the formatting the process above will respect the new format and convert the values. Happy computing!! [ This Message was edited by: Mark W. on 2002-02-20 15:48 ]

Feb 20th, 2002, 04:48 PM	#3
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	You can also try placing a 1 in any cell (even on another worksheet). Copy that cell. Select the bad number cells. Hit Edit-Paste special-Multiply. This multiplies the number by 1, which forces Excel to see those values as numbers.

Feb 20th, 2002, 06:09 PM	#4
Guest Posts: n/a	Maria, Use "Dreamboat's" suggestion, but do it in Access. In other words, modify your query to perform the "conversion" when you run your query. For example, if the offending field is called "Quantity" then modify your query like this: Qty: Quantity * 1 In this way you don't have to constantly do this conversion in Excel. enjoy Bariloche