Excel 2013 slow or hangs during "convert to number" process

Another work-around I've found:

1. Copy the data table
2. Paste it into a text editor like NotePad or TextPad
3. Leave all the pasted data highlighted.  Now re-copy the data from the text editor (ctrl+c)  **this step fixes the number-as-a-string issue
4. Paste the data back into Excel, right over the existing data that's highlighted (ctrl+V)

Any text data should be untouched and all of the numbers (stored as strings) will instantly convert to a number.

Excel 2013 has a problem interpreting numerical data when you open it from a file.  However, if you copy it from another source (i.e. the text editor) and paste it into Excel, it seems to know what to do with it.

Also note - merely copying the data from Excel and pasting into a new Excel window will not work!  Pasting it into the text editor and then re-copying removes the formatting data that's causing the problem.

My company has this exact same problem on every PC that has Excel 2013 installed. I even opened a support case on this but the support people said there's nothing they can do about it. "Excel is behaving normally". Then later on an engineer guy called me to have me reproduce the problem and said its the way Excel is programmed.  So there's no solution to this, other than use the older versions.

Nice going Microsoft... I love how all your software seems to be decaying.

Hello!

Don't hurry up to downgrade your Excel 2013 to 2010.

Fortunately, I found a workaround for the issue.

Feel free to use the following macro to convert values in the selected range to numbers.

It runs extremely fast.

Sub ConvertTextToNumber()

Application.ScreenUpdating = False

Application.EnableEvents = False

oldCalculation = Application.Calculation

Application.Calculation = xlCalculationManual

Dim r As Range

Set r = Application.Selection

rangeValues = r.Value

r.NumberFormat = "General"

r.Value = rangeValues

Application.Calculation = oldCalculation

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

If you want to reuse this macro in different workbooks, add it to the Personal Macro Workbook and then

assign a macro to a button on the Quick Access Toolbar or to a button in your own personal group on the ribbon.

Regards from Belarus,

   Andrei.

I am currently trying to run this operation on a set of 35,524 records with 2 columns. Excel has been hanging for around half an hour. Microsoft, why are you doing this to me? Do you think I have nothing better to do than wait unspecified amounts of time for your terrible products to do what they're supposed to do?

If I force Excel to shut down, I run the risk of losing stuff that I need. Thanks a bunch

My company has this exact same problem on every PC that has Excel 2013 installed. I even opened a support case on this but the support people said there's nothing they can do about it. "Excel is behaving normally". Then later on an engineer guy called me to have me reproduce the problem and said its the way Excel is programmed.  So there's no solution to this, other than use the older versions.

Nice going Microsoft... I love how all your software seems to be decaying.

Hello,

working on different files from 30.000 to 50.000 rows, it doesn't matter if they're .xls or .xlsx, when I select all records on a column and select "convert to number" Excel 2013 hangs for 3/4 minutes while CPU shows 50 to 100% of usage.

This behaviour is occasional, it affects 9 attempts over 10 while 1 attempt works fine and takes 4 seconds to correctly complete the task.

When I try using Excel 2010 with the same files, everything works fine and the task is completed in a few seconds.

.......

......

Hello Giorgio,

I tested the feature "Convert To Number" with a workbook that contains 3 worksheets with 30,000 values, 60,000 values and 480,000 values and can confirm that this issue is reproduced in Excel 2013, whereas "Convert To Number" works very fast in Excel 2010.

You can find the shared test workbook at http://1drv.ms/1N5iiFm

The following hard & soft configurations were used in the tests:

1. NEW config:

Software:

  Excel 2013, 32-bits (15.0.4763.1003)

  Windows 7 Pro with SP1, 64-bit

Hardware:

  2 years old HP notebook

  CPU - Intel i5-2450M 2.50 GHz

  RAM - 8 GB

2. OLD config:

Software:

  Excel 2010 SP1 (14.0.6112.5000)

  Windows XP SP3

Hardware:

  8 years old Toshiba notebook

  CPU - Intel Core 2 Duo, T5600, 1.83 GHz

  RAM - 2 GB

Here are my tests' results:

30,000 values

   OLD config - about 1 second

   NEW config - 27 seconds

60,000 values

   OLD config - 2 seconds

   NEW config - 55 seconds

480,000 values

   OLD config - 14 seconds

   NEW config - 9 minutes !!!

Microsoft, how did it happen that performance was much better in the previous version

of Excel on the old hardware than in the newer Excel on the faster hardware ?