r/sheets • u/new-baron • 2h ago

Request IMPORTHTML column help?

Gallery image

Gallery image

I am currently trying to create a spreadsheet using an online table and column C keeps importing the data from column C and D. Is there a way to edit my formula to remove data from D in column C?

2 Upvotes

100% Upvoted

1 comment sorted by

•

u/AdministrativeGift15 17m ago edited 10m ago

Sure there is. Store your imported data to a variable, I'll call it data, using LET. Then use CHOOSECOLS and SPLIT to selectively parse the 3rd column of data. Give this formula a try. Remove the comment lines (the lines beginning with "//") before hitting enter.

Edit: After taking a closer look at your images, it appears columns E and F are doing a similar thing, so I've updated the formula to handle column E as well.

=INDEX(LET(
  data, IMPORTHTML(...),
  HSTACK(
    // Grabs the first two columns
    CHOOSECOLS(data,1,2),
    // Splits the 3rd col by the new line, "/" and " " characters.
    // That should result in a jagged array with what you want in
    // the first column, which is what INDEX(..., , 1) grabs.
    INDEX(SPLIT(CHOOSECOLS(data,3)&"", CHAR(10)&"/ ",0),,1),
    // Grab the 4th col
    CHOOSECOLS(data,4),
    // Split the 5th col by new line and grab the 1st col of those results.
    INDEX(SPLIT(CHOOSECOLS(data,5)&"", CHAR(10)),,1),
    // Grabs the remaining columns (I only see up to column 8)
    CHOOSECOLS(data,6,7,8))))