vba 如何确保列中所有格式化为文本的 Excel 单元格实际上是
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9007404/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make sure all excel cells formatted as text in a column actually are
提问by datatoo
I have a column of content submitted by multiple users, generally pasted into a sheet, from multiple sources. This column has numbers that should always be formatted as text. In fact, no matter how this is done, there are always a few items that never have the indicator in the left corner warning that these are formatted as text (which we want to see in all cases)
我有一个由多个用户提交的内容列，通常粘贴到一个工作表中，来自多个来源。此列包含应始终格式化为文本的数字。事实上，不管怎么做，总会有一些项目在左上角从来没有指示符警告它们被格式化为文本（我们希望在所有情况下都能看到）
Checking the individual cell, it does show as formatted text, but in reality on an import into a datatable, if the indicator is missing, the datatype is imported as a number. Clicking after the number and hitting Enter will change the indicator to text.
检查单个单元格，它确实显示为格式化文本，但实际上在导入数据表时，如果指标丢失，数据类型将作为数字导入。在数字后面单击并按 Enter 会将指示符更改为文本。
How can I do that in VBA? I don't want to visit each cell, click on the end of the content and hit enter.Cutting and paste special in no combination reliably fixes these.
我怎样才能在 VBA 中做到这一点？我不想访问每个单元格，单击内容的末尾并按回车键。特殊的剪切和粘贴没有组合可靠地修复这些。
What does excel look at, which gets the format issue right with these text format warning indicators, and yet doesn't seem to get it right when you look at the cell format properties?
excel 看什么，它通过这些文本格式警告指示符正确解决了格式问题，但在查看单元格格式属性时似乎没有正确解决？
Excel 2003 but have had the same issue in later versions too.
Excel 2003 但在更高版本中也有同样的问题。
采纳答案by datatoo
What worked for me was to check the error indicator, which seemed more reliable than the cell format itself. This looks for anything missing the indicator and forces it to be text.
对我有用的是检查错误指示器，这似乎比单元格格式本身更可靠。这会查找缺少指标的任何内容并强制其为文本。
Unless someone knows something further concerning why this should NOT be done, it solves my issue.
除非有人进一步了解为什么不应该这样做，否则它解决了我的问题。
Sub check4textformat()
For Each cell In Range("E2:E15000")
If cell.Errors.Item(xlNumberAsText).Value = False Then cell.Formula = cell.Text
Next
End Sub
回答by Meir Cohen
Since your main target is not visiting each cell, I will suggest an easier way than VBA (actually, it's probably the easiest way).
由于您的主要目标不是访问每个单元格，我会建议一种比 VBA 更简单的方法（实际上，这可能是最简单的方法）。
- Select the desired column.
- Go to Data > Text to Columns.
- Select "Delimited" (should be already selected by default) and press "Next".
- Uncheck all delimiters and press "Next" (you can also leave the default state of "Tab" as the only delimiter).
- Under "Column Data Format", select "Text" and click "Finish".
- 选择所需的列。
- 转到数据>文本到列。
- 选择“Delimited”（默认情况下应该已经选中），然后按“Next”。
- 取消选中所有分隔符并按“下一步”（您也可以将“Tab”的默认状态保留为唯一分隔符）。
- 在“列数据格式”下，选择“文本”并单击“完成”。
Done. All the numbers in the column should be stored as text now.
完毕。列中的所有数字现在都应存储为文本。
回答by Wilhelm
You really only need to prepend the text indicator character "'" before every number. Assuming that your values are in the first column, in the first 120 rows, you can do it like this:
您实际上只需要在每个数字之前添加文本指示符“'”。假设您的值在第一列中，在前 120 行中，您可以这样做：
For i = 1 To 120
Me.Cells(i, 1).Value = "'" & Me.Cells(i, 1).Value
Next i
If the cell already has a text value, the prepending is ignored. When copying (and when obtaining the value in VBA) the "'" is completely ignored as it only indicates the "type" of the cell contents.
如果单元格已有文本值，则忽略前置。复制时（以及在 VBA 中获取值时），“'”将被完全忽略，因为它仅表示单元格内容的“类型”。
