人们使用Excel引用的最常见的烦恼之一是处理数字和文本在单元格中的格式。当数字无意中以文本格式输入电子表格时，这尤其令人讨厌。
发生这种情况时，计算和不同的公式就不能正常工作，或者根本不能工作。
在本文中，您将学习如何识别一列或一行数字何时实际格式化为文本，以及如何将文本转换为数字，以便它们再次用于公式和计算。这是每个人都应该知道的基本 Excel 技巧之一。(basic Excel tips)
单元格数据是文本还是数字？
您可以通过多种方式查看列或行中的一个数字或一组数字是否在Excel中格式化为文本。
最简单的方法是选择单元格，选择主(Home)菜单，然后在功能区的数字(Number)组下，注意下拉框中显示的数字格式。
如果下拉框显示“文本”，您就知道该单元格被格式化为文本格式。如果要使用Excel 公式对单元格执行(Excel formulas)数值计算(numerical calculations)，则需要先对其进行转换。
如果有人使用单元格中的撇号输入了文本格式的数字，您会看到一个绿色的小三角形，表示该值已作为文本输入。
注意(Note)：在单元格条目之前加上撇号会强制单元格格式为基于文本的。
如果您发现，使用上述任一方法，数字数据以文本格式输入Excel工作表，您可以使用以下任何方法将该文本转换为数字。
1.转换为数字
如果您需要使用撇号转换已输入Excel的数据，您可以使用“(Excel)转换(Convert)为数字(Number)”选项轻松地将其转换回数字格式。
1.首先(First)，选择要转换回数字格式的单元格。您会看到选区附近出现一个黄色菱形，中间有一个感叹号。
2. 选择此符号。从下拉列表中，选择转换为数字(Convert to Number)。
这会将您选择的所有基于文本的数字更新为通用(General)数字数据格式。
当您选择的所有数字在单元格中从左对齐切换到右对齐时，您就会知道它起作用了。
2.使用文本到列
在Excel(Excel)中将文本转换为数字的另一种简单方法是一次转换一整列值。您可以使用“文本到列(Column)”功能执行此操作。
1. 选择要从文本转换为数字的整列数据。
2. 从菜单中选择数据(Data)，然后在功能区的数据工具(Data Tools)部分中选择文本到列。(Text to Columns)
3. 在Wizard窗口中，保持默认Delimited被选中，然后选择Next。
4. 在下一个向导(Wizard)页面上，保持默认选项卡(Tab)处于选中状态，然后再次选择下一步(Next)。
5. 最后，在向导(Wizard)的最后一页，确保在列数据格式(Column data format)下选择了常规(General)。对于目标(Destination)字段，您可以选择一个您希望数字数据所在的新列，也可以保持当前选定的列不变。选择完成(Finish)。
现在您的数据将全部转换为数值，您可以在Excel公式和计算中使用这些数值。
注意(Note)：您会注意到实际的单元格格式不会从文本(Text)更改为常规(General)，即使值本身现在可以用作数字。但是，如果您将输出列设置为新列，您会注意到新列的格式设置为General。这只是一个表面问题，不会影响“文本(Text)”格式列中的数字的行为方式。
3.更改单元格格式
在Excel(Excel)中将文本转换为数字的最简单、最快的方法就是从主(Home)菜单更改单元格格式。
去做这个：
1. 选择所有要转换的单元格。如果要转换列中的所有单元格，可以选择整列（不包括标题）。
2. 选择主(Home)菜单，然后在功能区的数字组中，选择包含(Number)文本(Text)的下拉框。
3. 您将看到可供选择的格式列表。选择常规(General)以转换为数字格式。或者，如果您希望将这些特定数字格式应用于您的数字数据，您可以选择Number、Currency、Accounting或 Percentage 。(Accounting)
4. 使用粘贴值
如果您需要将包含数字的文本单元格移动到新的单元格或列中，您可以使用选择性粘贴(Paste Special)功能。
1. 选择要放置数字数据输出的空单元格组。从弹出菜单中选择设置单元格格式。(Format Cells)
2. 在打开的窗口中，确保选择General作为数字格式，然后选择OK。
3. 选择要从文本转换为数字的整列单元格，单击鼠标右键，然后选择复制(Copy)。
4. 选择您格式化的空列中的第一个单元格，右键单击该单元格并选择粘贴值(Paste Values)。您将看到以通用(General)数字格式粘贴的所有文本格式数字。
这是因为当您选择Paste Values时，它只粘贴源单元格中的值，而不是原始单元格格式。相反，它使用您在此过程的第一部分中配置的目标单元格格式。
5. 使用 VALUE 函数
Excel中有一个特殊功能，可以将格式为文本的数字转换为数值。这是VALUE函数。
要使用此功能，请选择您希望转换后的数字所在的单元格并键入：
=VALUE(G2)
将上面的“G2”替换为具有要转换的数字的单元格。如果要转换一整列数字，请仅从第一个单元格开始。
按Enter 键(Enter)，您会看到文本格式的数字已转换为通用格式的数字。
然后，您可以将空列的其余部分填充到该列的底部，VALUE公式也将转换原始列中的其余单元格。
使用这些选项中的任何一个重新格式化数字后，您可能需要在应用新格式后刷新单元格数据。
如您所见，在Excel中有多种方法可以将文本转换为数字。您选择的选项仅取决于您尝试放置输出的位置。这还取决于您更喜欢使用复制和粘贴、Excel公式还是菜单选项。
最终，这些选择中的每一个都会为您提供相同的最终结果。
5 Ways To Convert Text to Numbers In Excel
One of the most common annoyances that peoplе cіte with Excel is deаling with how numbers and text arе formatted in cells. It’s especially annoying when numbers inadvertently get entered into a spreadsheet in text format.
When this happens, calculations and different formulas don’t work quite right, or may not work at all.
In this article you’ll learn how to identify when a column or row of numbers are actually formatted as text, and how to convert text to numbers so that they’ll work in formulas and calculations again. This is one of those basic Excel tips everyone should know.
Is Cell Data Text Or Numbers?
There are several ways you can see if a number or set of numbers in a column or row is formatted as text in Excel.
The easiest way is to select the cell, select the Home menu, and under the Number group in the ribbon, note the number format displayed in the dropdown box.
If the dropdown box displays “text”, you know the cell is formatted as text format. If you want to perform numerical calculations on the cell using Excel formulas, you’ll need to convert it first.
In the case where someone has entered numbers in text format using the apostrophe in the cell, you’ll see a small green triangle indicating the value has been entered as text.
Note: Preceding a cell entry with an apostrophe forces the cell formatting to text-based.
If you’ve discovered, using either of the approaches above, that the numerical data is entered into the Excel sheet in text format, you can use any of the methods below to convert that text to numbers.
1. Convert To Number
If you need to convert data that’s been entered into Excel with an apostrophe, you can easily convert it back to number format using the Convert to Number option.
1. First, select the cells you want to convert back to number format. You will see a yellow diamond appear near the selection with an exclamation symbol in the middle.
2. Select this symbol. From the dropdown, choose Convert to Number.
This will update all of the text based numbers you’ve selected to the General numeric data format.
You’ll know it worked when all the numbers in your selection switched from being left aligned to right aligned in the cells.
2. Using Text to Column
Another easy way to convert text to numbers in Excel is by converting over an entire column of values at once. You can do this using the Text to Column feature.
1. Select the entire column of data that you want to convert from text to numbers.
2. Select Data from the menu, and then select Text to Columns in the Data Tools section of the ribbon.
3. In the Wizard window, keep the default Delimited selected and select Next.
4. On the next Wizard page, keep the default Tab selected, and select Next again.
5. Finally, on the last page of the Wizard, make sure General is selected under Column data format. For the Destination field, you can either select a new column where you want the number data to go, or just keep the current selected column as is. Select Finish.
Now your data will all be converted to numeric values, which you can use in Excel formulas and calculations.
Note: You’ll notice that the actual cell formatting doesn’t change from Text to General even though the values themselves can now be used as numbers. However, if you set your output column to a new column, you will notice that the formatting of the new column is set to General. This is only a cosmetic issue and doesn’t affect how the numbers in the “Text” formatted column behave.
3. Changing Cell Format
The easiest and fastest way to convert text to numbers in Excel is simply changing the cell formatting from the Home menu.
To do this:
1. Select all of the cells you want to convert. You can select an entire column (don’t include the header) if you want to convert all of the cells in a column.
2. Select the Home menu, and in the Number group on the ribbon, select the dropdown box with Text in it.
3. You’ll see a list of formats to choose from. Select General to convert to number format. Or you can select Number, Currency, Accounting, or Percentage if you want those specific number formats applied to your numerical data.
4. Using Paste Values
If you need to move text cells that contain numbers into a new cell or column, you can use the Paste Special feature.
1. Select the group of empty cells where you want to place your output of numeric data. Select Format Cells from the pop-up menu.
2. In the window that opens, make sure General is selected as the number format and select OK.
3. Select the entire column of cells you want to convert from text to numbers, right-click, and select Copy.
4. Select the first cell in the empty column you formatted, right-click the cell and select Paste Values. You’ll see all of the text formatted numbers pasted in the General number format.
This works because when you select Paste Values, it pastes only the values from the source cell and not the original cell formatting. Instead, it uses the destination cell formatting, which you configured in the first part of this process.
5. Using The VALUE Function
There is a special function in Excel that’ll convert a number formatted as text into a numeric value. This is the VALUE function.
To use this function, select the cell where you want the converted number to go and type:
=VALUE(G2)
Replace “G2” above with the cell that has the number you want to convert. If you’re converting an entire column of numbers, start with the first cell only.
Press Enter and you’ll see that the text-formatted number has been converted to a General-format number.
You can then fill the rest of the empty column to the bottom of that column and the VALUE formula will convert the rest of the cells in the original column as well.
After using any of these options for reformatting your numbers, you may need to refresh cell data after applying the new formatting.
As you can see, there are a number of ways to convert text to numbers in Excel. The option you choose just depends on where you’re trying to place the output. It also depends whether you prefer using copy and paste, Excel formulas, or menu options.
Ultimately, each of these choices provides you with the same end result.
