VBA 在字符串中吃掉我的零？
所以这是一个我以前从未遇到过的问题。我从定义为文本的单元格导入 ISIN（例如 DE0002635307）。我需要用它来引用该名称的单元格。因此：
sub ISINWriter
dim ISIN as String
ISIN = ThisWorkbook.Sheets(i).Cells(j, 4).Value()
ThisWorkbook.Sheets(i+1).Cells(f, 4).Formula = "=" & ISIN
End Sub
对于大多数 ISIN，这都可以正常工作，除非连续有 4 个或更多零。如果发生这种情况 - 例如 FR0000120073 - 它将“=FR120073”写入单元格。它只是吃掉字符串内的零！有什么想法吗？
我使用 Excel 2010 和 Windows 7。
非常感谢。
So here is a problem that I have never come across before. I Import an ISIN (for example DE0002635307) from a cell, which is defined as a text. I need to use this to reference to a cell by that name. So:
sub ISINWriter
dim ISIN as String
ISIN = ThisWorkbook.Sheets(i).Cells(j, 4).Value()
ThisWorkbook.Sheets(i+1).Cells(f, 4).Formula = "=" & ISIN
End Sub
For most of the ISINs this works fine, except if there are 4 or more zeros in a row. If that happens - for example FR0000120073 - it writes "=FR120073" into the cell. It just eats the zeros INSIDE the string! Any ideas?
I use Excel 2010 and Windows 7.
Thanks a lot.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您使用名称管理器尝试创建名为“FR0000120073”的命名范围（或更准确地说是命名公式），那么您应该会发现出现错误。类似于（来自我机器上的 Excel 2007）：
线索位于第三个原因的最后部分。 FR120073 是当今 16Kx1m 单元格工作表中的有效单元格地址。
不过，上面的第一个原因可能有用：
_FR0000120073是一个有效的名称。你能用那个吗？If you use the Name Manager to try to create a named range (or more accurately a named formula) called "FR0000120073" then you should find that you get an error. Something like (from Excel 2007 on my machine):
The clue is in the last part of the third reason. FR120073 is a valid cell address in these days of 16Kx1m cell worksheets.
The first reason above may be useful, though:
_FR0000120073is a valid name. Could you use that?编辑：删除我的最后一个答案，这样就有意义了。
基本上，您的单元格引用是一个字母数字值。以A1为例，A是列，1是行。出现此问题的原因是 0001 与 1 相同。因此对单元格 A001 的引用将与单元格 A1 相同。
看起来 Excel 有一些内置功能可以从单元格引用中删除前导 0。
EDIT: Removing my last answer so this makes sense.
Basically your cell references are an AlphaNumeric value. Take A1 for example A is the column 1 is the row. This issue is occuring because 0001 is the same as 1. so a reference to cell A001 is going to be the same as cell A1.
It looks like Excel has some built in functionality to remove leading 0's from your references to cells.