Hello :).


I am trying to import a table from a web-page where some of the values are
written for example as 7 -35.
Using Data/get external data/new query. Using excel 2000 prof.

I am making a querry for importing the table into excel. Then the 7 - 35
value in transformed into the number12966.
I understand this have to do with the way excel is reading dates. But 7 - 35
in the table is not meant to be a date, but showing two different numbers (7
and 35).
I therefore have tryed to go to format, numbers and set different formats to
the cell to make it show exactly the number om the webpage (7 - 35).
The closest i was to make it work is to set the format defined to d-m (In
format/cells/self defined). Then the cell is showing 1-7. This is not
correct eigther, but in other cells this give the equal number as in the web
table. The most cells is correct but som are not.
---
Now i also have tryed to format the cell as text, but that did not work.

Is here someone with the same problem?.

Thanks if someone can give me a little advice.

Kjell

Kjell,

If you are simply interested in correcting the display, then format the
cells for custom, using

m - yy

but that would only apply to cells where the second number results in an
invalid month/day combination.

7-31

would need to be formatted

m - d

As an alternative, you could use a helper column that uses formulas to check
all possiblities. For example,

=IF(YEAR(A1)<2004,TEXT(A1,"m - yy"),TEXT(A1,"m - d"))

might work, and can be copied down to match your data.

=IF(YEAR(A1)<2004,"'" & TEXT(A1,"m-yy"),"'" & TEXT(A1,"m-d"))
would allow you to copy and pastespecial values better than the previous
formula, which would allow you to remove the original data and be done with
it.

Of course, you could write a macro to do all this as well.

HTH,
Bernie
MS Excel MVP

"Kjell Nygaard" wrote in message
...
Hello :).


I am trying to import a table from a web-page where some of the values are
written for example as 7 -35.
Using Data/get external data/new query. Using excel 2000 prof.

I am making a querry for importing the table into excel. Then the 7 - 35
value in transformed into the number12966.
I understand this have to do with the way excel is reading dates. But 7 -
35
in the table is not meant to be a date, but showing two different numbers
(7
and 35).
I therefore have tryed to go to format, numbers and set different formats
to
the cell to make it show exactly the number om the webpage (7 - 35).
The closest i was to make it work is to set the format defined to d-m (In
format/cells/self defined). Then the cell is showing 1-7. This is not
correct eigther, but in other cells this give the equal number as in the
web
table. The most cells is correct but som are not.
---
Now i also have tryed to format the cell as text, but that did not work.

Is here someone with the same problem?.

Thanks if someone can give me a little advice.

Kjell