How to change Format of a Cell to Text using VBA

Question

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?

Answer 1

To answer your direct question, it is:

Range("A1").NumberFormat = "@" 

Or

Cells(1,1).NumberFormat = "@" 

However, I suggest 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.