Cell format wrong on import from HTML: text becomes scientific notation integer

J

Jim Rech

Has Excel got its nucleus firmly...

You mean are there any switches or registry settings that can modify how
Excel behaves when importing a text file? Not that I know of. When Excel
doesn't do what you need you probably have to use a macro instead. You can
find some generic macros for that (like
http://www.cpearson.com/excel/imptext.htm) but you might need something
highly customized to your file spec.
 
J

Jeff Melvaine

I am importing tables from HTML files. One of the fields is an alpha ID
which frequently takes the form 1E<99>. Excel invariably imports this as an
integer in scientific notation, which makes restoration of the 4 digit text
value unreasonably difficult. There could be up to 50 IDs in the 1E<99>
format in a single column.

Even when the column format is subsequently changed to text, repairing the
damage is like removing glass splinters with tweezers. Trying to
Edit>Replace 1E+ with 1E doesn't work, either because Excel thinks it would
be mangling a formula (in which case Edit>Find 1E+, Find Next, and deleting
the plus sign works), or because the + is not literally part of the string,
but an artifact of Excel thinking that the value is still really an integer
in scientific notation (in which case Edit>Find also gives up).

Preformatting all cells in the target worksheet as text doesn't help.
Importing in full html format doesn't help either; amazingly, even when the
1E<99> is part of a hyperlink, Excel still manages to mangle it as a number.

Has Excel got its nucleus firmly shoved up its contractile vacuole, or is
there a workaround for this problem? I am using Excel 2002.
 
J

Jeff Melvaine

Two technical corrections to the above: 1E<99> is of course alphanumeric,
and Excel imports it in a numeric format, which would likely be float rather
than integer for high values of the exponent. From memory, Edit>Find on 1E+
failed for exponents in the mid twenties and above.
 
Top