如何使用VBA将单元格的格式更改为文本 [英] How to change Format of a Cell to Text using VBA

问题描述

我在Excel表中有一个duration列。它的单元格格式总是改变—我想把持续时间从分钟转换成秒，但由于单元格的格式，它总是给我不同的答案。

我以为在进行转换之前，我可以将该单元格格式转换为文本，以便它将它视为文本值，而不是尝试自动格式化。 / p>

目前，我将所有数据复制到记事本中，然后将其保存回Excel表以删除所有以前的格式。有没有办法使用VBA自动将单元格的格式设置为文本？

解决方案

为了回答您的直接问题，它是： / p>

 范围（A1）。NumberFormat =@
  

或

  Cells（1,1）.NumberFormat =@ 
  

但是，我建议将格式更改为您想要显示的内容。这允许您在单元格中保留数据类型，并可轻松使用单元格公式来操作数据。

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.

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?

解决方案

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.

这篇关于如何使用VBA将单元格的格式更改为文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！