Excel的LEN()函数对大于20个字符的数字返回5 [英] Excel's LEN() function returns 5 on numbers longer than 20 characters

问题描述

当我的数字超过20个字符时，excel中的LEN()函数将返回5.

When I have numbers longer than 20 characters the LEN() function in excel returns 5.

我已经在台式机和手机上进行了尝试，并获得了相同的结果.

I've tried this on my desktop and my mobile and get the same result.

当我使用评估公式"时，它显示它从"LEN(100000000000000000000)"(20个零)变为"5".

When I use "Evaluate Formula" it shows it going from "LEN(100000000000000000000)"(20 zeros) to "5".

在21个零的情况下，将数字格式化为科学数字，因此它取自"LEN(1E + 21)".到"5".

In the case of 21 zeros it formats the number as scientific so it goes from "LEN(1E+21)" to "5".

所以我想excel以21个字符存储数字并带有科学计数法，而LEN()会计算数字的长度.

So I guess that at 21 characters excel stores numbers with the scientific notation and LEN() calculates the length of that.

恰好20个零的异常仍然显示为"100000000000000000000"，在评估公式"中只是一个奇怪的情况?

The anomaly of exactly 20 zeros still showing as "100000000000000000000" in "Evaluate Formula" is just a weird case?

下面是我用来测试数据的表.

Below is the table of data I was using to test this.

<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Input</th><th>Len output</th><th>formula</th><th>input formatting</th><th>actual length (dp is just formatting not actually part of the number)</th></tr></thead><tbody>
 <tr><td>10000000000000000000.0</td><td>20</td><td>=len(A2)</td><td>number 1dp </td><td>20</td></tr>
 <tr><td>1000000000000000000000</td><td>5</td><td>=len(A3)</td><td>number no dp</td><td>22</td></tr>
 <tr><td>10000000000000000000</td><td>20</td><td>=len(A4)</td><td>Text</td><td>20</td></tr>
 <tr><td>100000000000000000000</td><td>21</td><td>=len(A5)</td><td>Text</td><td>21</td></tr>
 <tr><td>100000000000000000000.0</td><td>5</td><td>=len(A6)</td><td>number 1dp </td><td>21</td></tr>
 <tr><td>100000000000000000000.00</td><td>5</td><td>=len(A7)</td><td>number 2dp</td><td>21</td></tr>
 <tr><td>10000000000000000000.00</td><td>20</td><td>=len(A8)</td><td>number 2dp</td><td>20</td></tr>
 <tr><td>1E+19</td><td>20</td><td>=len(A9)</td><td>scientific</td><td>20</td></tr>
 <tr><td>1E+20</td><td>5</td><td>=len(A10)</td><td>scientific</td><td>21</td></tr>
</tbody></table>

推荐答案

您可以先将数字转换为文本，然后再输入 LEN()

You can convert the number to text before taking the LEN()

=LEN(TEXT(A1,"#")) 

这篇关于Excel的LEN()函数对大于20个字符的数字返回5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！