Trying to use Google Sheets importHTML() to import a table. It forces content to a date format

Question

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.

Answer 1

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

• IMPORTXML() Importing Data as Date

Answer 2

You can do this in a single formula using array_constrain, query, index and regexreplace:

={ARRAY_CONSTRAIN(IMPORTHTML("http://www.sportsnet.ca/hockey/nhl/standings/", "table"),100,10),arrayformula(REGEXREPLACE(query(text(IMPORTHTML("http://www.sportsnet.ca/hockey/nhl/standings/", "table"),"M-D-YYYY"),"select Col11,Col12,Col13"),"(\d-\d-)(\d\d\d)(\d)","$1$3"))}