字符串转换与TEXT公式字符限制? [英] String conversion with TEXT formula character limit?
问题描述
例如,我想转换:
5145350002005000080
To:
5145-350002-00500-0080
我使用的公式是:
=文本(A1,0000-000000-00000-0000)
我收到的输出是:
5145-350002-00500- 0000 / p>
为什么最后4个字符0000而不是0080?是否有字符限制,或是我的公式不正确?
从大数算术:
Excel中的限制是一个数字中的15位有效数字。输入一个16
的信用卡号码,1234567890123456将成为
1234567890123450。
其实,即使514535000200500 1111 将导致 5145-350002-00500-0000
。
此外,看看在选择输入单元格的公式栏中 - 对于我的Excel 2007,我看到:
希望是有帮助的)
:
作为解决任务的解决方案 - 将您的数字格式化为文本,并使用以下公式:
= LEFT(A1,4)& - & MID(A1,5,6)& - & MID(A1,11,5)& - & RIGHT(A1,4)
I am attempting to format a single number stored as a text value.
For example, I would like to convert:
5145350002005000080
To:
5145-350002-00500-0080
The formula I am using is:
=text(A1,"0000-000000-00000-0000")
The output I am receiving is:
5145-350002-00500-0000
Why are the last 4 characters "0000" instead of "0080" as I would expect? Is there a character limit, or is my formula incorrect?
Quote from Large Number Arithmetic:
The limit in Excel is 15 significant digits in a number. Enter a 16 digit credit card number and 1234567890123456 will become 1234567890123450.
Actually, even 5145350002005001111 will result in 5145-350002-00500-0000
.
Moreover, take a look at formula bar when your input cell is selected - for my Excel 2007 I see:
Hope that was helpful)
EDITED:
As a solution to solve the task - keep your numbers formatted as text and use the following formula:
=LEFT(A1,4)&"-"&MID(A1,5,6)&"-"&MID(A1,11,5)&"-"&RIGHT(A1,4)
这篇关于字符串转换与TEXT公式字符限制?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!