Format Number to Text

[Guest]

Is it possible to format numbers into text from vba code? The situation is
that we insert data from a database into an Excel spreadsheet. One of the
columns is an address column. The address column often has normal address
data, but sometimes it will only contain a number like 1435, which causes
Excel to treat that cells data as a number. So when we import the data back
from Excel the column is misread because of the different formats for the
cells. The database export unfortunately wipes out the column type, so
changing its format type to text does nothing. Can the format command in VBA
change a cells type?

Thanks for the help,
ACFalcon

 

[Guest]

Hi,

Columns(1).numberformat="@" will format Col A to text and then numbers are
treated as text.

HTH

 

[Dave Peterson]

But when that str(1234) hits A1 (and A1 is formatted as General), then A1 will
still be a number.

You could force it to be text:

Range("A1") = "'1234"
(included the leading apostrophe)

or you could format the cell as Text first.

with range("a1")
.numberformat = "@"
.value = 1234
end with

 

[Leith Ross]

Hello ACFalcon.

You can convert a number in VBA to a string by using the STR function.

EXAMPLE
Range("A1") = Str(1234)

The value in A1 is now text and not a number. As for the Forma
statement changing a cell's type, the answer is no. All cells ar
Variant data types.

Sincerely,
Leith Ros