Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Convert text to numbers
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? -- Regards vipa |
#2
|
|||
|
|||
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<atrediffmail<dotcom Bangalore |
#3
|
|||
|
|||
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). "vipa2000" wrote: 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? -- Regards vipa |
#4
|
|||
|
|||
Thanks Karthik, I built it into a macro and used a button to re-issue as and
when needed. -- Regards vipa "Karthik" wrote: 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<atrediffmail<dotcom Bangalore |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert excel file into ASCII text file with alignment? | Excel Discussion (Misc queries) | |||
convert a range of lowercase text to upper text or vice versa | Excel Worksheet Functions | |||
How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Convert text to numbers | Excel Discussion (Misc queries) |