Trying to import football (soccer) league tables from the web - goals format

[Anthos]

Hi guys, I'm trying to import some football league tables using the "from the web".
Everything is fine except the "goal" column where the original table displays the goals in the form of "16:8" (goals for & against).
Excel imports this column as "time" format. The cell shows "16:08" which is actually 4:08:00pm. Now all I want to do is convert this cell into 16:8 and later use the left and right functions to split the "for" and "against" goals into different cells.

I want them as numbers because I want to do some calculations.

Any tips?

Thanks

 

[martindwilson]

how are you importing it? did you set the field to text?

 

[Anthos]

Using Data\From Web. This way I can set up lots of tables and then just refresh them all.
I then choose the table to import but I can't find any option to set any part of the table as text (not the same as importing txt files).

Thanks martindwilson,

 

[martindwilson]

what's the url? but you can split it with

=LEFT(TEXT(A1,"hh:mm"),2)

<tbody>
</tbody>

=RIGHT(TEXT(A1,"hh:mm"),2)

 

[Anthos]

The URL is http://www.flashscore.com/standings/OC7HxAXs/dzkFoSgt/ and I check the first table you'll see.

Something is messed up with the format because your formula returns "hh" and not the "16".
Thanks for trying to help me.

 

[martindwilson]

well it should work
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	Atletico River Plate	37	19	13	5	64:28:00	70	there could concievably be	*	64	28
2	* * *	*	*	*	*	*	*	*	more than 99 goals so	*	*	*
3	2	Instituto	37	19	13	5	128:101	70	a slight change	*	128	101
4	* * *	*	*	*	*	*	*	*	*	*	*	*
5	3	Quilmes	37	19	12	6	60:21:00	69	*	*	60	21
6	* * *	*	*	*	*	*	*	*	*	*	*	*
7	4	Rosario Central	36	20	9	7	46:27:00	69	*	*	46	27
8	* * *	*	*	*	*	*	*	*	*	*	*	*
9	5	Patronato	37	15	11	11	41:35:00	56	*	*	41	35
10	* * *	*	*	*	*	*	*	*	*	*	*	*
11	16	Guillermo Brown	36	9	11	16	46:60	38	*	*	46	60
12	* * *	*	*	*	*	*	*	*	*	*	*	*
Sheet1

 

[Anthos]

Thanks martindwilson.
I believe it has to do with my settings because the imported format is "2/1/1900 4:28:00 μμ" (the μμ is the Greek pm). Strange because both Windows and Excel are in English by default. Will look this up and I'm sure your formula will work.

Will post as soon as I find out something on this.
Thanks again for your willingness to help me.

Anthos

 

[Anthos]

Just to add that at the office the formula works as I suspected. It has to do with the way excel imports the data at my home pc.

 

[ghynes]

hello.
i have tried to import data from web to excel using the url above but it is not giving me the table data. am i doing something wrong? how do i get the table data to excel?