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?
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.
OR:
Could you try an autofit first:
Excel_Obj.Columns("A:V").EntireColumn.AutoFit
for large numbers that display with scientific notation set format to just '#'
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 = "@"
Source: Stackoverflow.com