在Excel中将数字转换为数字

There are Excel formulas and User Defined Functions (UDF) that can change numbers into words. Those are handy if you're typing a number into a workbook, and want the written amount to be shown, as it might appear in a cheque. Have you ever tried to do the opposite – change words to numbers in Excel?

有一些Excel公式和用户定义函数(UDF)，可以将数字转换为单词 。 如果您要在工作簿中输入数字，并且希望显示书面金额(可能会出现在支票中)，则这些功能非常方便。 您是否尝试过相反的操作–在Excel中将单词更改为数字？

单词到数字 (Words to Numbers)

It's a tricky process to change words to numbers in Excel, and Excel MVP, Jerry Latham, has created an Excel UDF – WordsToDigits – to help you out.

在Excel中将单词更改为数字是一个棘手的过程，Excel MVP Jerry Latham创建了一个Excel UDF – WordsToDigits –以帮助您。

With the WordsToDigits UDF, you can create a worksheet formula to translate words into their numeric value. For example:

使用WordsToDigits UDF，您可以创建工作表公式以将单词转换为它们的数值。 例如：

• Five hundred fifty-two becomes 552
  552是552
• One dollar and 27 cents becomes 1.27
  一美元和27美分变成1.27

In the first example, the words are in cell B2, and the formula in cell C2 is:

在第一个示例中，单词在单元格B2中，而单元格C2中的公式为：

=WordsToDigits(B2)

= WordsToDigits(B2)

You can format the formula results in any number format you want, such as general numbers, numbers with thousands separators, or currency.

您可以将公式结果设置为所需的任何数字格式，例如通用数字，带千位分隔符的数字或货币。

请参考工作表单元格 (Refer to Worksheet Cells)

Although the WordsToDigits UDF is quite flexible, it only works with cell references, not text strings. For example, this formula in cell C2:

尽管WordsToDigits UDF非常灵活，但它仅适用于单元格引用，不适用于文本字符串。 例如，单元格C2中的以下公式：

=WordsToDigits("twelve")

= WordsToDigits(“十二”)

will result in a #VALUE! Error.

将产生#VALUE！ 错误。

However, if you type "twelve" in cell B3, and refer to that cell in the formula, the result is correct. Here is the formula that's entered in cell C3:

但是，如果您在单元格B3中键入“十二”，并在公式中引用该单元格，则结果是正确的。 这是在单元格C3中输入的公式：

=WordsToDigits(B3)

= WordsToDigits(B3)

WordsToDigits提示 (WordsToDigits Tips)

Jerry passed along these tips, for working with the WordsToDigits UDF:

杰瑞(Jerry)传递了以下技巧，以使用WordsToDigits UDF：

• It can handle numbers up to 999,999,999,999,999.
  它最多可以处理999,999,999,999,999个数字。
• The accuracy of any decimal portion decreases as the number of digits increases. But at normally used value ranges such as up through millions, this should not be a significant issue.
  小数部分的精度随着位数的增加而降低。 但是，在通常使用的值范围(例如高达数百万美元)下，这应该不是什么大问题。
• • six and 7/100
    六和7/100
  • twelve hundred dollars and 27 cents
    1200美元和27美分
• Misspelling of numeric words will result in them being interpreted as zero, so typos can give inaccurate results.
  数字单词的拼写错误将导致它们被解释为零，因此错别字可能会给出不正确的结果。

Note: The WordsToDigits UDF is based on the short scale system of naming large numbers.

注意：WordsToDigits UDF基于命名大数字的小规模系统 。

使用WordsToDigits UDF进行实验 (Experiment With the WordsToDigits UDF)

You can download Jerry's workbook, to see the UDF code, and his examples of how to use the function.

您可以下载Jerry的工作簿，以查看UDF代码以及有关如何使用该函数的示例。

Go to Jerry's Sample Files page on my website. Then, in the VBA section, look for JL0001 -- Words to Numbers in Excel.

转到我网站上的Jerry的示例文件页面 。 然后，在“ VBA”部分中，查找“ JL0001-Excel中的数字单词”。

To test the UDF, you can create your own entries on the workbook's Sample Entries sheet.

要测试UDF，可以在工作簿的“样本条目”表上创建自己的条目。

If you have questions or comments, please share them with Jerry, in the comments below. ___________________

如果您有任何问题或意见，请在下面的评论中与Jerry分享。 ___________________

在Excel中将数字转换为数字 (Words to Numbers in Excel)

翻译自: https://contexturesblog.com/archives/2011/10/21/words-to-numbers-in-excel/