From Access to Excel data problem...

[Waxaholic]

I have data that was imported into Excel2000 via Access2000. Unfortunately the data always comes in as "TEXT" format. This doesn't allow me to perform any math on the data. Is their a way to convert (globally) the "TEXT" data into "NUMBER" format data? I have tried using the Format Cells option but that has absolutely no effect. As for converting the data while it is in Access, well, that is out of the question because it is a live connection being updated all the time and it comes in as text for some reason. So, i need to do the conversion within Excel if possible. Anyone have any ideas or can point me in the right direction.

Much appreciated,

Wax

 

[Dreamboat]

Hi Wax. Try this:

1. Put a one (1) in any blank cell (even another sheet or workbook; it doesn't matter).

2. Select all of the cells with "text numbers" in them.

3. Hit Edit-Paste Special-Multiply.


By multiplying by one, it forces Excel to see the text as values, hence, numbers. This may mess up dates and other stuff; just reformat the columns again after you do that.


techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[Waxaholic]

Hey Dreamboat, that did the trick. Thank you very much.

Wax