How to calculate error for squared data?

When I copy and paste an html table of numbers taken from a web based stock report, I cannot add formulae into adjacent cells to calculate additional data as I get a 'value' error. How can I make this work?

• Answer:

  I think iwnit probably has the right answer, but to take it one step further, just "changing format" (by selecting the cells and "making them" the new numeric format that you want) won't fix the problem by itself, because the numbers will still have been entered as text, so they will still BE text until they are manually changed -- or that is done by macro. You have to re-examine each of the values individually and force Excel to re-read them as "numbers", which it isn't doing right now. You could have a macro do that for you: Create the following Module in the VBA editor (more on that later if you don't understand how to do that): Sub FixNumberFormat() ' Convert text-entered number to number format, or ' vice versa Dim cell As Range For Each cell In Selection cell.Value = cell.Value cell.NumberFormat = cell.NumberFormat Next cell End Sub If you don't know how to create a new module, then do this: 1. Make your workbook the active window. That is, be sure that you're working there. 2. From the menu select Tools / Macro / Macros and in the 'Macro' window that comes up type in the name "FixNumberFormat" (no spaces in the name) and click Create. 3. Paste the macro text above into the window that appears. (This is the VBA -- Visual Basic for Applications -- Editor.) Your macro must begin with "Sub [MacroName] ()" and finish with "End Sub", so you can overwrite the 'begin' and 'end' tags in the macro window with the text above. 4. Navigate back to your worksheet containing the values to be re-evaluated / changed to numbers. Make sure that you have already modified the format of the cells to Number in the normal way. 5. Select the cells to be re-evaluated and then do Tools / Macro / Macros. Click on the FixNumberFormat macro and "Run". 6. The numbers should now be re-read and evaluated as numbers, and not text. All this macro is doing is exactly what you would be doing yourself by re-examining each cell, one by one, and hitting F2 and Enter (with no other change). But the macro does it faster than you could possibly do it. And with less chance for error, too.