Google Sheets ImportHTML – Import Table Without Forcing Date Format

google sheetsimporthtml

I am trying to get a table into a sheet. The table contains data that looks like this:

4-0-2

This is wins-losses-ties. It comes in as a date. This is the command I use:

IMPORTHTML("http://www.sportsnet.ca/hockey/nhl/standings/", "table")

This is the site:

http://www.sportsnet.ca/hockey/nhl/standings/

I have tried reformatting things in the sheet, but the damage has been done.

Best Answer

Formulas

For columns 1-10

On cell A1 add the following formula:

=ARRAY_CONSTRAIN(IMPORTXML("http://www.sportsnet.ca/hockey/nhl/standings/",
 "(//table)[1]//tr"),2000000,10)

For columns 11-13

On cells K1 to M1 add the following formula on each cell 

=ArrayFormula(
TEXT(IMPORTXML("http://www.sportsnet.ca/hockey/nhl/standings/", 
"(//table)[1]/*/*/*["&COLUMN()&"]"),"d-m")&
IF(ISNUMBER(IMPORTXML("http://www.sportsnet.ca/hockey/nhl/standings/", 
"(//table)[1]/*/*/*["&COLUMN()&"]")),
"-"&right(TEXT(IMPORTXML("http://www.sportsnet.ca/hockey/nhl/standings/", 
"(//table)[1]/*/*/*["&COLUMN()&"]"),"y"),1),)
)

For column 14

On cell N1 add the following formula

=IMPORTXML("http://www.sportsnet.ca/hockey/nhl/standings/", 
"(//table)[1]/*/*/*["&COLUMN()&"]")

Explanation

IMPORTHTML and IMPORTXML assigns the data type before adding the values to the spreadsheet.

The formulas shown on the previous section could be used as a workaround.

See also

Related Topic