如何将文本转换成数字?

I have an Excel report which contains several columns of text and several columns of numbers which are stored as text.

我有一个Excel报告，其中包含几个文本列和几个数字列，这些列作为文本存储。

Is there an easy way to convert those numbers that are stored as text to numbers, without affecting the actual text data?

是否有一种简单的方法可以将存储为文本的数字转换为数字，而不会影响实际的文本数据?

8 个解决方案

#1

I find that the easiest and quickest way to convert "numbers stored as text" into numeric numbers is

我发现把“作为文本存储的数字”转换成数字的最简单、最快的方法是

Select any blank cell
选择任何空白单元
Copy that cell (Ctrl+C)
复制单元格(Ctrl + C)
Select the range which contains the data you want to convert (it's OK if the range includes nonnumeric data as well)
选择包含要转换的数据的范围(如果该范围也包含非数值数据，那么也可以)
Use "Paste Special" with operation "add" (I guess "subtract" would work too)
使用“粘贴特殊”和“添加”操作(我猜“减法”也可以)

Besides being quick, this has the advantage of converting in-place.

除了速度快之外，它还有就地转换的优势。

"Paste Special" is in the Edit menu of "classic" versions of Excel (2003 and earlier), or in the Clipboard section of the Home tab of "ribbon" versions of Excel (2007+).

“粘贴专用”是在“经典”版本的Excel(2003年及更早版本)的编辑菜单中，或者在“ribbon”版本的Excel(2007年+)的Home选项卡的剪贴板中。

#2

Use the VALUE function if you just want that text as a number in a different cell.

如果你只是想让文本作为一个数字在不同的单元格中，那么可以使用VALUE函数。

#3

Another workaround is to add zero to force a type conversion: If the cell A1 contains '5', yet ISNUMBER(A1) returns FALSE, ISNUMBER(A1+0) will return TRUE if A1 can be case into a number.

另一个解决方案是添加0以强制类型转换:如果单元格A1包含“5”，而ISNUMBER(A1)返回FALSE，如果A1可以为大小写，ISNUMBER(A1+0)将返回TRUE。

#4

I'm not sure I understand your dilemma, but there are at least two solutions to your immediate question:

我不知道我是否理解你的困境，但至少有两种方法可以解决你眼前的问题:

Format the cells that need to be numbers as "number" as opposed to text. You can highlight the cells you want --> right click --> Format as number. You can set the number of decimal and specific formatting from there.

将需要为数字的单元格格式化为“数字”，而不是文本。您可以突出显示您想要的单元格——>右键单击——>格式作为数字。您可以从那里设置十进制数和特定的格式。
Create a new column that multiplies your text/number column times 1 and set the format to this new column as number.

创建一个新的列，将文本/数字列乘以1，并将格式设置为数字。

#5

Personally if there are just a few numbers I just press F2 on each cell and press enter, without any changes. That makes Excel think you have edited the cell and turned it into a number.

就我个人而言，如果只有几个数字，我只需在每个单元上按F2，然后按回车键，没有任何改变。这让Excel以为你已经编辑了单元格，并把它变成了一个数字。

#6

Cannot get these solutions to work properly with a mixed column of alpha and numeric where the numerics are text copied from a web page. A contributor (D. Mabutt) on About.com posted this VBA code

无法使这些解决方案在阿尔法和数字的混合列中正常工作，其中数字是从web页面复制的文本。一个贡献者(D. Mabutt)在About.com上发布了这个VBA代码。

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

That code will throw an error on the alpha cells, so this should be added:

该代码会对alpha单元格产生错误，因此应该添加以下代码:

Sub Enter_Values()
For Each xCell In Selection
If IsNumeric(xCell) = True Then
    xCell.Value = CDec(xCell.Value)
    Else
     End If
 Next xCell
End Sub

#7

Here is the solution I found for the situation I'm in.

这是我在这种情况下找到的解决方案。

I run a report weekly that requires copying and pasting approximately 7000 rows of data exported from a system. Recently some changes were made so that the numbers were coming out stored as text. The data includes a selection of actual text entries and the rest numbers stored as text. This data gets copied and pasted into a master template which then feeds other sheets/reports, but I needed the numbers to be stored as numbers. Due to the volume of the data and the fact that I'm not the only one performing this task (and some of the others are not Excel savvy) I needed to try and automate this process. I tried the =A1*1 formula and it worked for the numbers but the text I got a #VALUE! error. After much fiddling, I came up with the formula below and it works perfectly!

我每周运行一个报告，需要复制和粘贴从系统导出的大约7000行数据。最近做了一些修改，使数字以文本的形式存储出来。数据包括实际文本条目的选择和作为文本存储的其余数字。这些数据会被复制并粘贴到一个主模板中，该模板将提供其他的表/报告，但是我需要将数字存储为数字。由于数据量很大，而且我不是唯一执行这个任务的人(还有一些人不熟悉Excel)，我需要尝试并自动化这个过程。我尝试了A1*1公式，它适用于数字，但是文本我得到了#值!错误。经过大量的修改，我想出了下面的公式，它非常有效!

=IF(AND(A1="Yes"),"Yes",IF(AND(A1="Not applicable"),"Not applicable",IF(AND(A1="No"),"No",A1*1)))

=如果(和(A1 =“是”),“是”,如果(和(A1 =“不适用”),“不适用”,如果(和(A1 =“不”),“不”,A1 * 1)))

#8

The other answers didn't help me, but I found out that at least in Excel 2010 (i guess it would be similar in other versions), you can use the little green triangle in the upper left of the affected cells after you've selected them.

其他答案对我没有帮助，但我发现至少在excel2010(我猜在其他版本中也会类似)中，您可以在选中受影响单元格后使用左上角的小绿色三角形。

What it doesn't tell you however, is that for it to work with multiple cells, you have to start your selection with a cell that contains a green triangle. The last cell may be any cell (the last cell can be anything). If you start with an non-green cell, the yellow exclamation mark will never appear.

但是，它没有告诉你的是，为了让它与多个细胞一起工作，你必须用一个包含一个绿色三角形的单元来开始你的选择。最后一个单元格可以是任何单元格(最后一个单元格可以是任何东西)。如果你从一个非绿色的单元开始，黄色的感叹号将永远不会出现。

I've made a simple table in an empty worksheet where every cell was converted to text via Format > Format Cell. I then entered the values below (I've marked the rows that contain green triangles with ^ in the diagram).

我在一个空工作表中创建了一个简单的表，其中每个单元格通过格式>格式单元格转换为文本。然后我进入下面的值(我标志着行包含图中绿色三角形^)。

  |  A  |  B  |
--------------
1 | abc |     |
2^| 1   |     |
3^| 2   |     |
4^| 3   |     |
5^| 4   |     |
6^| 5,6 |     |
7 | 7.8 |     |
8^| 9   |     |
9 | xyz |     |
--------------

Now, you have to start your selection in A2 or A8 and drag the selection to the other cells for it to work. Starting at A9 or A1 does not work. Starting in between would work, but this way you cannot select all cells in one go.

现在，您必须在A2或A8中启动选择，并将选择拖到其他单元格中以使其工作。从A9或A1开始不行。从中间开始是可行的，但是这种方式不能一次选择所有单元格。

Also the recognition of cells might depend on your locale - for countries that use points instead of commas, your results may vary (cell A6 vs. A7).

此外，单元格的识别可能取决于您的语言环境——对于使用点而不是逗号的国家，您的结果可能会有所不同(单元格A6与A7)。

#1