Preventing Scientific Data Interpretation

[Audri]

When opening a text file containing security IDs(mixture of numbers and characters), any security ID containing a "E" in the 8th position, followed by numbers, is interpreted as scientific notation. This goes further than cell formatting, the cell data has been altered. I have tried pre-setting the cell format to text prior to getting the external data, but no luck. BTW: MS Word opens this file just fine.

Any ideas?

- Audri

 

[BabyTiger]

Hi,

You might want to try using the following formula to convert the number into text:

=text(A1,"0")

HTH

 

[Legacy 98055]

If that does not work, set it up as a string with an apostrophe in front of the first character. Such as AABBCCDE4524
Throw it into a string not a variant like this "'AABBCCDE4524"
That should bully Excel into taking it as a text string.
Hope It Works!
Tom

 

[Dave Hawley]

Hi Audri


Try Importing the text file via Data>Get external data>Import text file. The last step of this Wizard allows you to easily nominate the Column format to be used.