Number changed to Text

[nate axtell]

When I convert a Number formatted value to Text format I see that the number
just looks like it's formatted as text. I have to then double click on it
or edit it some how then click away for it to be saved (stored) as text.
For the "inbetween" state before it gets stored as text if I resize the
column to make the column smaller then the number is wide the number will
change to scientific notation. Where as if it is a Number format it turns
to ####..., and if it is stored as text the overlapping part either
disappears or lays on top of the next cell to its right. Is there a way to
change the inbetween state so that it does not get converted to scientific
notation and acts like one of the other two ways?
Thanks,
Nate

 

[Greg Wilson]

Putting an apostrophe in front seems to work; e.g., change
123456 to '123456. Excel ignores the apostrophe and treats
it as text. The text value will spill over into the cell
or cells to the right if they are blank instead of
converting to scientific notation or ####'s.

Regards,
Greg

 

[nate axtell]

yup, i know about the apostrophe. But, I am looking for a way to globally
change the cells. There could be a lot of cells in this situation in a
document we have. I have developed a web based importing tool that reads
the excel file into a recordset using the Jet driver. I find that numbers
which are in this "Confused" state and 7-digits or longer are converted to
scientific notation when read from the Excel file into the recordset. In
the recordset they are considered text so there is no conversion that can be
done to get the original number out. For the driver I use IMEX = 1, to
force types to be text.
thanks,
nate