Number format from Excel 3

[andyjsav]

Please can someone help, I've copied & pasted numbers into excel from Access, now they are not functioning as numbers but as text, even when I change the format of the cells to numbers, pasting values also doesnt work, is there any quick way round this without manually overwriting everything
its Excel and Access 2002

Thanks - Andy

 

[xlbo]

Formatting won't change anything - it only affects what you SEE. To change to numbers, use the "*1 fix"

In a blank cell, enter the number 1
press CTRL+C
Select all the "textual numbers" you want to convert and go to Edit>Paste Special
Select Values and Multiply from the options
et voila - your numbers should be numbers

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[andyjsav]

Geoff, you're a star

Thanks - Andy

 

[mkrinke]

I have the same problem, but the field is in a worksheet created by a query. Everytime I refresh the query the fix is gone...
Any ideas on fixing in code or some other trick?

Thanks, Mark - new at this Excel stuff

 

[xlbo]

summat like this:

For each c in selection
c.value = c.value*1
next c

Or, just record yourself doing the "*1 fix" as described above



Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mkrinke]

I was just working on a macro to multiply by one... but I'm curious about the other option:

For each c in selection
c.value = c.value*1
next c

Where do you put this code to run it everytime the query is refreshed?

Thanks, Mark

 

[xlbo]

Ahaaaa - now you're asking. I've just solved this myself......and it's a pain in the a*se
You have to use the AfterRefresh event of the querytable (otherwise data can still be being refreshed whilst the code is running)
Unfortunately, to expose the AfterRefresh event, you need to create a class module for it and initialise it every time you open the file. Trust me.....if it isn't essential to the project, don't bother

an easier way may be to return the data, starting in col B (ie leave col A blank)
Enter this in A2
=value(B2) (where B2 holds the textual number)
autofill it down to the bottom of the queryset
Right click anywhere in the data area and choose DataRange Properties. Within there, check the box that says "Copy down adjacent formualae. This should mean that the formula in A2 is always copied down to the last cell in the queryset and will provide a numeric version of your textual number

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mkrinke]

thank you!!!
(this site is damn helpful!)

Mark

 

[ladyck3]

Oh someone a week or so ago gave another hint on how to do this, rather than the "*1" fix, use the

Copy a blank cell
Paste Special > Add

WOrks just as slick as enter a 1, copy then Paste Special and choose Multiply....

Just a little FYI for y'all....

Laurie

 

[mkrinke]

Also worked. Thanks !