Preventing Scientific Data Interpretation

Audri · Mar 25th, 2002, 08:29 PM

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 · Mar 25th, 2002, 09:02 PM

Hi,

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

=text(A1,"0")

HTH

Tom Schreiner · Mar 25th, 2002, 10:15 PM

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 · Mar 26th, 2002, 12:20 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 25th, 2002, 08:29 PM	#1
Audri New Member Join Date: Mar 2002 Posts: 5	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

Mar 25th, 2002, 09:02 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Hi, You might want to try using the following formula to convert the number into text: =text(A1,"0") HTH

Mar 25th, 2002, 10:15 PM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Mar 26th, 2002, 12:20 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.