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

Hi,

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

HTH

"ACFalcon" wrote:

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

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 wrote:

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 Format
statement changing a cell's type, the answer is no. All cells are
Variant data types.

Sincerely,
Leith Ross

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=493932

--

Dave Peterson

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

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=49393