Convert text to numbers

[Guest]

I have a spreadsheet with numerous columns. Data is pasted in monthly into
one of the worksheets. Code is written on a separate worksheet , my report
worksheet, to interrogate this data. I have found that I am getting strange
results and found that two of my columns are coming in as text rather than
numbers. I don't want users to have to convert the columns manually. I have
tried coding this by using 1 as a multiplier when pasting, but this causes me
a problem. i have set my code up to interrogate 30000 rows ever time. This
way i am sure the user doesn't have to worry about the length of the pasted
data. when I use the multiplier those cells that were blank end up with a
zero in and my report goes to pot. If there a slick way to convert text which
are numbers to numbers?

 

[Karthik]

Hi Vipra

Do this: Before you multiply your numbers with 1 select only non blank
cells... How??

As usual write 1 in any cell and copy it.
Select your cells (all of them - 3000 rows). Now go to
Edit> Go To..>Special... >Select the "Constants" option and click OK.
Now you see only non blank cells are selected.
Now do a paste special. Edit > Paste Special > and choose multiply. (u
already know this)
Job done in under 10 seconds.....

Thanks
Karthik Bhat
Karthikbhat<at>rediffmail<dot>com
Bangalore

 

[Guest]

I'm not sure why numbers would read in as text, other than there may have
been a change made to "Autocorrect " that change those numbers to text.
(autocorrect is located under TOOLS).

 

[Guest]

Thanks Karthik, I built it into a macro and used a button to re-issue as and
when needed.