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?

"Whose Cell format always Changes"?? Please show us an example. — Jean-François Corbett, Nov 25 '11 at 07:31

Justin Self · Accepted Answer · 2021-05-13T19:08:28.123

124

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.

edited May 13 '21 at 19:08

answered Nov 25 '11 at 20:03

Justin Self

6,137
3
33
48

score 13 · Answer 2 · edited Jun 23 '20 at 12:02

13

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

edited Jun 23 '20 at 12:02

Teamothy

2,000
3
16
26

answered Apr 22 '15 at 19:56

Dan McSweeney

131
1
3

1

You really help me with this tip about set NumberFormat before set the value! Txs! – Zini Apr 14 '18 at 13:45

score 8 · Answer 3 · answered Nov 25 '11 at 20:02

8

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

answered Nov 25 '11 at 20:02

Jon

433
3
6
24

30

There is no need to activate or select before you change the NumberFormat - just use something like Sheets(1).Columns(1).NumberFormat = "@" – Gaijinhunter Nov 26 '11 at 04:24

score 1 · Answer 4 · answered Feb 21 '19 at 18:08

1

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

answered Feb 21 '19 at 18:08

Mark Freeman

21
1

score 1 · Answer 5 · edited Mar 15 '22 at 20:06

1

To prevent Scientific Notation

With Range(A:A)
    .NumberFormat = "@"
    .Value = .Formula
End With

edited Mar 15 '22 at 20:06

Stefano Sansone

2,377
7
20
39

answered Mar 14 '22 at 13:02

Defgha

11
1

Or `.Value = "whatever"` – vapcguy Jun 23 '23 at 17:47

How to change Format of a Cell to Text using VBA

5 Answers5

Linked