Inserting numbers (especially with leading zeroes) as text
rng.numberFormat = "@" changes the data type of the content of a range to text. This allows to insert text that represent numbers, especially such with leading zeroes, as text, which left-aligns them.
When doing that, Excel still believes this is an error and displays a green triangle in the cell. To turn that triangle off, the
ignore property of errors(xlNumberAsText) needs to be set to true. option explicit
sub main() ' {
'
' Text is converted to number: leading zeroes are removed
' and number is right aligned:
'
cells(1,1) = "0001"
'
' Text is inserted as number THEN converted to string
' (which still removes leading zeroes)
'
cells(2,1) = "0002"
cells(2,1).numberFormat = "@"
'
' Format of cell is changed to text THEN text is inserted.
' This keeps leading zeroes but also has green triangle that
' indicates an error in the cell
'
cells(3,1).numberFormat = "@"
cells(3,1) = "0003"
'
' Remove green triangle by setting errors(…).ignore
' property to true:
'
cells(4,1).numberFormat = "@"
cells(4,1) = "0004"
cells(4,1).errors(xlNumberAsText).ignore = true
activeWorkBook.saved = true
end sub ' }
Github repository about-MS-Office-object-model, path: /Excel/Range/numberFormat-insert-number-as-string.bas
