3

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.

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

1

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"))}

enter image description here

1
  • Thanks to Aurielle and Rubén. Aurielle you nailed it. Works perfectly! – John Scarlett Nov 24 '16 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.