Excel 中的数字表示
我正在 Excel 2007 上构建一个 VBA 程序，输入长串数字 (UPC)。现在，该程序通常运行良好，但有时数字字符串似乎会转换为科学记数法，我想避免这种情况，因为我随后会查找它们。
因此，我想将文本框输入视为精确的字符串。没有科学记数法，没有数字解释。
从相关的角度来看，这真的很奇怪。我有两个完全相同的 UPC ：两者都产生相同的值（据我或任何文本编辑器所知），但其中一个值给出了成功的 Vlookup，而另一个则没有。
有人对此有什么建议吗？感谢您抽出时间。
I'm building a VBA program on Excel 2007 inputing long string of numbers (UPC). Now, the program usually works fine, but sometimes the number string seems to be converted to scientific notation and I want to avoid this, since I then VLook them up.
So, I'd like to treat a textbox input as an exact string. No scientific notation, no number interpretation.
On a related side, this one really gets weird. I have two exact UPC : both yield the same value (as far as I or any text editor can tell), yet one of the value gives a successful Vlookup, the other does not.
Anybody has suggestions on this one? Thanks for your time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在 Excel 中，看起来像数字的长字符串可能会很麻烦。如果您没有对“数字”进行任何数学计算，那么它实际上应该被视为文本。正如您所发现的，当您想要强制 Excel 将某些内容视为字符串时，请在其前面添加撇号。
VLOOKUP 有几个常见问题。您发现的额外空格可以通过使用诸如
TRIM 函数之类的公式来避免，该函数将删除这些无关的空格。 VLOOKUP 的另一个常见问题是一个参数是字符串，另一个参数是数字。我在导入数据时经常遇到这种情况。您可以使用 TEXT 函数执行 VLOOKUP，而无需更改原始数据，
在尝试在 B 列中查找之前，将 A1 转换为五位数字字符串。当然，如果您的数据确实一团糟，您可能需要
Long strings that look like numbers can be a pain in Excel. If you're not doing any math on the "number", it should really be treated as text. As you've discovered, when you want to force Excel to treat something as a string, precede it with an apostrophe.
There are a couple of common problems with VLOOKUP. The one you found, extra whitespace, can be avoided by using a formula such as
The TRIM function will remove those extraneous spaces. The other common problem with VLOOKUP is that one argument is a string and the other is a number. I run into this one a lot with imported data. You can use the TEXT function to do the VLOOKUP without having to change the raw data
will convert A1 to a five digit string before it tries to look it up in column B. And, of course, if your data is a real mess, you may need