Question posted 2011 · +29 upvotes
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?
Accepted answer +55 upvotes
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.
2 code variants in this answer
- Variant 1 — 1 lines, starts with
Range("A1").NumberFormat = "@" - Variant 2 — 1 lines, starts with
Cells(1,1).NumberFormat = "@"
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
- How do I slice an array in Excel VBA? +31 (2008)
excel-vba solutions on this site
— top 1%.