用公式更改小数点分隔符 [英] Changing decimal separator with formula
问题描述
我与来自多个国家/地区的团队一起工作，有时我收到的excel文件中的分隔符与我使用的分隔符不同.有什么公式可以做到吗?我当然可以使用宏，但是公式会容易得多，因为我不是公司中唯一需要此宏的人，而且对于一般的Joe Public来说，宏会太复杂.
I work with teams from multiple countries and sometimes I receive excel files that have another decimal separator than the one I use. Is there any formula to do that? I certainly could use a macro, but a formula would be much easier because I am not the only person in my company in need of this and a macro would be too complicated for the general Joe Public.
请记住，我可能有多个具有相同数字的分隔符，例如3.000.000,00的千分之二有2个点，小数点有一个逗号.这就是为什么我觉得很困难.
Please keep in mind that I may have multiple separators in the same number, for example, 3.000.000,00 has 2 points for the thousands and millions, and the comma for the decimals. That is why I am finding it difficult.
推荐答案
或者，您可以使用 NUMBERVALUE 函数.
Alternatively, you can use the NUMBERVALUE function.
例如，如果我在单元格 A1 中有3.000.000,00，我可以在单元格<中输入 = NUMBERVALUE(A1，，"，.")code> B1 获取号码
For example, if I have 3.000.000,00 in cell A1 I can enter =NUMBERVALUE(A1,",",".") in cell B1 to get the number
参数是
-
文本.要转换为数字的文本.
Text. The text to convert to a number.
十进制分隔符.用于分隔结果的整数和小数部分.
Decimal_separator. The character used to separate the integer and fractional part of the result.
Group_separator.用于分隔分组的字符的数量，例如成千上万数千.
Group_separator. The character used to separate groupings of numbers, such as thousands from hundreds and millions from thousands.
此功能在Excel 2013和更高版本或Mac或更高版本的Excel 2011中可用.我在Excel 2013环境中工作，有时忘记使用它来限定我的答案.感谢 Doug 指出版本要求.
This is available in Excel 2013 and later or Excel 2011 for Mac or later. I work in an Excel 2013 environment and sometimes forget to qualify my answers with that. Thanks to Doug for pointing out the version requirements.
特别是:
Excel 2016，Excel 2013，用于Mac的Excel 2016，用于Mac 2011的Excel，在线Excel，用于iPad的Excel，用于iPhone的Excel，用于Android平板电脑的Excel，用于Mobile的Excel，用于Android手机的Excel
Excel 2016, Excel 2013, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel Mobile, Excel for Android phones
这篇关于用公式更改小数点分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
