Converting a number (17 characters) to text (Scientific Notation)

[MikeL]

Here’s my dilemma and challenge…

… This is through Automation…

I’m receiving an XML file that has excel formatting…

It’s very easy to import, the problem comes in when I import large numbers
(17 – 20 characters long)… They should be imported as text, but they are
importing as numbers, so they appear as scientific notation… When I go to
create a file from the data, the numbers are being written in the scientific
notation and not as text…

I am unable to control the content of the XML file. I do know the number is
in a certain column…

Also, when I double click on a cell, I can edit it and place a single quote
and it will store the number correctly… But, when I do it through automation,
it won’t convert…

We are using excel 2003… In the process of upgrading to 2007, but won’t be
for another 6 months…

Fustrating...

Your help would be greatly appreciated..

Thanks,
Michael

 

[Gary Brown]

1st of all, only the first 15 digits will convert. The rest will be '0's.
Example, importing 12345678901234567890 to Excel will convert to
12345678901234500000.

To convert an entire column of numbers to text quickly, you can use
something like...
Selection.TextToColumns _
Destination:=Selection.Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, xlTextFormat), _
TrailingMinusNumbers:=True

 

[Dave Peterson]

I'm not sure how you're importing that XML file, but if you want to preserve all
17 digits, maybe you can bring the data in as text and then clean up the stuff
that doesn't belong.

 

[MikeL]

Dave, Can't thank you enough for the reminder aoubt the 17 didget
truncation... (Saved alot of rework here...) I could not figure out a way
to import as text... (no filed definitions like open workbook...) But, when
I tried importing the XML in access, it recognized it as text, and the import
worked flawlessly... Again, because of the reminder, I went a different
route... If you didn't nuge us in the right direction, it would have been a
disaster.... (Thanks for going the extra mile... and making sure that we
understood...)

 

[Dave Peterson]

Glad you found a solution to the problem.

Dave, Can't thank you enough for the reminder aoubt the 17 didget
truncation... (Saved alot of rework here...) I could not figure out a way
to import as text... (no filed definitions like open workbook...) But, when
I tried importing the XML in access, it recognized it as text, and the import
worked flawlessly... Again, because of the reminder, I went a different
route... If you didn't nuge us in the right direction, it would have been a
disaster.... (Thanks for going the extra mile... and making sure that we
understood...)