Importing data. Problem with datatype in column

[Rolf Barbakken]

Hi

I'm importing data from a webpage. All data comes into the spreadsheet, and
most is fine, but I have a column for "status" that contains one or more
single-digit numbers. If what is imported is "1.2" (two statuses), Excel
interprets it as a date (1. February). If I change the datatype of the
column after import to Text I get the datevalue (39845).

How can I avoid this problem? Is it possible to force Excel just to leave
the data alone as it is and not interpret it?

I cannot change what's in the webpage or the database it is generated from.

Any help appreciated.

- Rolf

 

[Rolf Barbakken]

Is it possible to format e.g. the rows to the number format before you
import the data? That sometimes helps me

I tried this just now. I changed the format of the column to Number with two
decimals. This only caused the column to display 39845,00 where it
previously showed "1. February". A difference, but not a solution.

Thanks.

 

[David Biddulph]

You obviously missed the part where Kasper said "format e.g. the rows to the
number format before you import the data?"

"before", not "after".

Personally I would choose text, rather than number format, but you need to
ensure that the data comes in with that format, so if you are importing
external data you need to specify that format for the relevant columns in
the import dialogue. Once it is stored as a different format, changing the
display format will change only the display, not the stored value.

 

[Rolf Barbakken]

I obviously didn't. I have tried changing the cell format to both Text and
Number before importing. Neither works.

What you are describing ("specify that format for the relevant columns in
the import dialogue") sounds like importing from a file, because you don't
get the option of changing column data types in the dialogue for import from
web.

What does work, though, is changing one little option in the Import from web
dialogue, called "Disable date recognition" which does what it says for the
entire dataset. That helps. Don't know why I didn't see that before.

Thanks, though.

--
-Rolf

You obviously missed the part where Kasper said "format e.g. the rows to
the number format before you import the data?"

"before", not "after".

Personally I would choose text, rather than number format, but you need to
ensure that the data comes in with that format, so if you are importing
external data you need to specify that format for the relevant columns in
the import dialogue. Once it is stored as a different format, changing
the display format will change only the display, not the stored value.