1. #1
    SKX007
    SKX007's Avatar Become A Pro!
    Join Date: 08-27-11
    Posts: 23
    Betpoints: 234

    Web query with Excel for mac

    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.

  2. #2
    TomG
    TomG's Avatar Become A Pro!
    Join Date: 10-29-07
    Posts: 500

    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.

  3. #3
    TomG
    TomG's Avatar Become A Pro!
    Join Date: 10-29-07
    Posts: 500

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

  4. #4
    SKX007
    SKX007's Avatar Become A Pro!
    Join Date: 08-27-11
    Posts: 23
    Betpoints: 234

    Quote Originally Posted by TomG View Post
    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).

  5. #5
    TomG
    TomG's Avatar Become A Pro!
    Join Date: 10-29-07
    Posts: 500

    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.

  6. #6
    bbrendo224
    bbrendo224's Avatar Become A Pro!
    Join Date: 03-11-10
    Posts: 2
    Betpoints: 1152

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

  7. #7
    SKX007
    SKX007's Avatar Become A Pro!
    Join Date: 08-27-11
    Posts: 23
    Betpoints: 234

    Quote Originally Posted by TomG View Post
    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.

    Quote Originally Posted by bbrendo224 View Post
    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?

Top