How to change Format of a Cell to Text using VBA

Solution 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.

Solution 2:

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

Solution 3:

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 = "@"

Solution 4:

for large numbers that display with scientific notation set format to just '#'