在Excel中将文本转换为数字

问题：如何将填充有字符数字的单元格转换为数值，以便可以使用Excel数学公式中的值。 ( Question: How do I convert cells filled with character numbers to numeric values so I can use the values in Excel math formulas. )

I recently had to add a column of numbers in Excel that were copied and pasted from a table in a web page. Because the numbers are represented by text in the web page (that is, the number "10" is actually "Hex 3130"), a Sum function for the column simply results in a zero value.

最近，我不得不在Excel中添加一列数字，这些数字是从网页中的表中复制并粘贴的。 因为数字是由网页中的文本表示的(也就是说，数字“ 10”实际上是“十六进制3130”)，所以该列的Sum函数只会得出零值。

You can find a lot of web pages (including Microsoft pages) that simply give you advice that doesn't work. For example, this page ...

您可以找到很多网页(包括Microsoft页面)，这些网页只会为您提供无效的建议。 例如，此页面...

http://support.microsoft.com/kb/291047

http://support.microsoft.com/kb/291047

... gives you seven methods. The only one that actually works is to retype the value manually. (Gee, thanks, Microsoft. I never would have thought of that.) The most common solution I found on other pages is to Copy the cells and then use Paste Special to paste the Value. That doesn't work either. (Tested on Excel 2003 and Excel 2007.)

...给您七种方法。 唯一有效的方法是手动重新输入该值。 (Gee，谢谢，Microsoft。我从未想过。)我在其他页面上找到的最常见的解决方案是复制单元格，然后使用选择性粘贴粘贴值。 那也不行。 (在Excel 2003和Excel 2007上测试。)

The Microsoft page provides a VBA Macro to do the job ("Method 6"):

Microsoft页面提供了一个VBA宏来完成这项工作(“方法6”)：

Sub Enter_Values()
   For Each xCell In Selection
      xCell.Value = xCell.Value
   Next xCell
End Sub

It doesn't work either, but all you have to do is make one change and it does work:

它也不起作用，但是您要做的就是做一个更改就可以了：

For Each xCell In Selection
   xCell.Value =CDec(xCell.Value)
Next xCell 

It's not rocket science. I can't understand why so many pages have it wrong.

这不是火箭科学。 我不明白为什么这么多页面有错误。

翻译自: https://www.thoughtco.com/convert-text-to-number-in-excel-3424223