如何使用 VBA 将单元格的格式更改为文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8265350/
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 change Format of a Cell to Text using VBA
提问by Code Hungry
I have a "duration" column in an Excel sheet. Its cell format always changes — I want convert the duration from minutes to seconds, but because of the cell formatting it always gives me different answers.
我在 Excel 工作表中有一个“持续时间”列。它的单元格格式总是在变化——我想将持续时间从分钟转换为秒，但由于单元格格式，它总是给我不同的答案。
I was thinking that before doing the conversion I could convert that cell format to text so that it will consider that as text value and not try to auto-format it.
我在想，在进行转换之前，我可以将该单元格格式转换为文本，以便它会将其视为文本值，而不是尝试对其进行自动格式化。
Currently I am copying all data into Notepad and then saving it back to the Excel sheet to remove all of the previous format. Is there a way to automate setting a cell's formatting to text using VBA?
目前我正在将所有数据复制到记事本中，然后将其保存回 Excel 工作表以删除所有以前的格式。有没有办法使用 VBA 自动将单元格的格式设置为文本？
回答by Justin Self
To answer your direct question, it is:
要回答您的直接问题，它是：
Range("A1").NumberFormat = "@"
Or
或者
Cells(1,1).NumberFormat = "@"
However, I suggest making changing the format to what you actually want displayed. This allows you to retain the data type in the cell and easily use cell formulas to manipulate the data.
但是，我建议将格式更改为您实际想要显示的格式。这允许您保留单元格中的数据类型并轻松使用单元格公式来操作数据。
回答by Dan McSweeney
One point: you have to set NumberFormat property BEFORE loading the value into the cell. I had a nine digit number that still displayed as 9.14E+08 when the NumberFormat was set after the cell was loaded. Setting the property before loading the value made the number appear as I wanted, as straight text
一点：您必须在将值加载到单元格之前设置 NumberFormat 属性。当加载单元格后设置 NumberFormat 时，我有一个九位数字仍然显示为 9.14E+08 。在加载值之前设置属性使数字显示为我想要的，作为直接文本
OR:
或者：
Could you try an autofit first.
Excel_Obj.Columns("A:V").EntireColumn.AutoFit
你能不能先试试自动适应。
Excel_Obj.Columns("A:V").EntireColumn.AutoFit
回答by Jon
Well this should change your format to text.
那么这应该将您的格式更改为文本。
Worksheets("Sheetname").Activate
Worksheets("SheetName").Columns(1).Select 'or Worksheets("SheetName").Range("A:A").Select
Selection.NumberFormat = "@"
回答by Mark Freeman
for large numbers that display with scientific notation set format to just '#'
对于以科学记数法设置格式显示为仅“#”的大数字
