Import Data from Web - Syntax issue

[Tayta]

Guys

I've created a spreadsheet which calculates scores for Soccer/Football predictions based on correctly predicted scores against actual results.

As part of this, I would like to display in my sheet the list of results from the previous week along with the scores actualy scored. I will then use the actual scores to work out how many points I would have earned based on my predicted scores.

I have my data source here and have inported this table into Excel no problem using the Data from Web tool.

http://www.soccervista.com/results-C...12-824335.html

However, when I look at the row that shows what the scores were (E), the data is in an odd format. What should look like a ratio on the actual website (example 2:1 or 1:1) ends up looking like this... 02:01 and 01:01.

I have tried using =LEFT and =RIGHT, even =MID to just take part of the cell that I need and put it into another cell that I can then use later, for example =LEFT(E2,5) which I hoped would show the 5th characted in the cell in this case of it were 02:01 I would expect to see '1'.

But this isn't the case....I get very different results. Bizarrely if I tell my =LEFT statement to show 8 characteds starting from the left, it gives me a long string of numbers which it is apparently getting from the cell E2! Even though I I can only see 02:01 in it!

I hope that makes sence above. Sorry for the essay, I was trying to make sure I explained properly what the problem is.

All I want is to be able to extract the score results as shown in the web site into two different cells for use against an IF statement.

Appreciate any help

 

[Hermanito]

I'm not sure if you can intervene before Excel pulls the data in, but it seems like some of the data is recognized by Excel as being dates and/or times, and are automatically converted.
This behaviour can be a real pain to solve... sorry I can't be of more help

 

[Tayta]

Yeah, I think it must be when excel gets the data and puts it into the column, because the data from the website shows as expected. It’s how Excel is interpreting it that is causing me the headache. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[John_w]

http://www.soccervista.com/results-C...12-824335.html

However, when I look at the row that shows what the scores were (E), the data is in an odd format. What should look like a ratio on the actual website (example 2:1 or 1:1) ends up looking like this... 02:01 and 01:01.

Excel is importing the score as a time in HH:MM:SS format (only HH:MM is shown in the cell, but the full HH:MM:SS with SS as 00 is shown in the formula bar). To extract the separate scores, use:

=HOUR(E2) and =MINUTE(E2)

 

[Tayta]

John_w you are a legend!!

Thank you so much!!!! Works perfectly