Thread: Converting text to number is taking ages

Hi,

I have just converted a range of numbers, that were stored as text, to number. I did this by clicking on the exlamation symbol, shown at the top of the selected range and then clicked "Convert to Number".

The range contained about 2200 cells and it has taken Excel 2007 almost a half hour to complete this operation. In Excel 2003 this was much faster.

I also tried multiplying by 1 (Paste special ...) and that only took about 1 second to complete.

Am I missing some setting which causes Excel 2007 to re-calculate the entire workbook after each conversion to number?

Any advise is appreciated.

Erwin

Hi DonkeyOte,

There are no functions in the workbook that I know of. Sheet1 contains the range of numbers that I converted. Sheet2 contains only values and some formulas adding/subtracting cells (e.g.: =A1+B1, nothing fancy). The values that I was converting are not precedent to any formula (at least they weren't at the time of conversion).

There was a filter applied to the Sheet2, but I tried it again without the filter applied, but the result was the same.

Nope,

I fired up Excel with a blank workbook. Then pasted values from another Excel workbook into Sheet1 and removed some columns that I didn't need. Then I pasted grid output from a SQL query (SSMS 2008) into Sheet2 and added some formulas there. No formatting applied.

No, it takes me to the last cell of the original range that I pasted into the worksheet (I deleted columns B-N and P-Q, so I kept column A and the original column O is now column B). CTRL+END takes me to Q2200.

Sorry, no improvement.

I restarted Excel and pasted the range into a new blank workbook. This time I saved the file first (this indeed cleans up the deleted areas). However converting to number is still as slow as it was before. CPU usage varying between 50 and 80%, but sometimes topping 90% (Intel dual core 2,4GHz with 4GB RAM).

Hi DonkeyOte,

it turns out that saving the file after pasting the values and then restarting Excel solves a lot. The range is now converted in the blink of an eye. So, although the range that I copied from the original Excel file contained only values, is it possible the Excel keeps some reference to the formulas that were in this original file? That one was packed full of formulas (none of which were dependent on the values that were copied).

It seems like a bug to me.

Hi DonkeyOte,

I now know what I must do next time I'm converting a range of values. Or I can use the old "multiply by 1" trick.

Thanks for taking the time to try and solve this problem with me.

Regards,
Erwin

I found a solution, if it is still needed. Use text to column. Highlight the column with the numbers stored as text, open text to columns, select delimited, uncheck all the delimiters, make sure the formatting is general, and then click finish. Works like a charm. I don't know why it took me so long to think us this as a solution, but I've been annoyed by your exact problem.