Web query with Excel for mac

[SKX007]

I am importing some tennis stats with the "get external data" function in Excel for mac. All the stats come in nicely into excel, except 2 columns. Cell C3 should show Win Loss record of "14-2" but shows "14MFeb".
I have tried to change the format of the cells, but nothing works. When I import the same file into Open office, everything is perfect, even the Win Loss columns? I am thinking this must be something with the language settings in Excel for mac, but don't know how to fix it.
Any help much appreciated.

[TomG]

i remember having this problem once before as well. the dash is very confusing to excel which defaults to treating it like a date. i don't think there's a way to prevent excel from importing it as a date. what you'll need to do is import as is and use excel's text functions and grab the values using something like =FIND("-",A1) and then pull out the numbers on either side of the dash and rebuild the data correctly. fun.

[TomG]

ps, i'm on a pc so it's not a language setting on mac vs. pc afaik, it's excel

[SKX007]

i remember having this problem once before as well. the dash is very confusing to excel which defaults to treating it like a date. i don't think there's a way to prevent excel from importing it as a date. what you'll need to do is import as is and use excel's text functions and grab the values using something like =FIND("-",A1) and then pull out the numbers on either side of the dash and rebuild the data correctly. fun.

TomG, thanks for helping out. I would like to try your solution, but not sure I understand how you mean regarding the above bolded part? How can I pull out the numbers, when I can't see them? I can't see the dash either. All I can see is 14MFeb(it should be 14-2).

[TomG]

use the value of the cell not what is being displayed. you may need to tinker with the cell formatting using the custom date formats such as d-mmm. also try changing the web query settings, check the cell, "preserve cell formatting." good luck it's a sonofabitch.

[bbrendo224]

On the "new web query" screen (the one where you input the web address), try clicking options then checking "disable date recognition"

[SKX007]

use the value of the cell not what is being displayed. you may need to tinker with the cell formatting using the custom date formats such as d-mmm. also try changing the web query settings, check the cell, "preserve cell formatting." good luck it's a sonofabitch.

Thanks Tom, I will try this later. Still hope there is a solution to do this in a normal way.

On the "new web query" screen (the one where you input the web address), try clicking options then checking "disable date recognition"

Oh, I don't think I can use the new web query function, I don't have the ODBC driver. I just use the microsoft sample files that are in the query folder and change the web address there and then "run saved query". Do you think my problem will be solved if I buy the ODBC driver and disable date recognition?