When importing HTML into Google Sheets, there are issues with the order by and select col functions as well as the header

Question

I am using this function =QUERY(INDEX(SUBSTITUTE(IMPORTHTML (I2 & "?refresh=" & I6,I3,I4), "*", )), "SELECT Col1, Col2, Col3, Col4 ORDER BY Col1 ASC")

This is returning data with the headers in between the table.

Also, can we use offset to avoid headers at all, if yes what would be proper syntax Link to importing page

https://ipowala.in/ipo-subscription-status/

Answer 1

You might as well do all of the work inside a QUERY

=ARRAYFORMULA(
  QUERY(
   SUBSTITUTE(
    IMPORTHTML("https://ipowala.in/ipo-subscription-status/?refresh="&I6,"table",1),
    "*",""),
    "select Col1, Col2, Col3, Col4
     where not Col1 = 'IPO Name'
     order by Col1",0))

This will ditch the headers

If you wanted to remove the * while retaining the actual values, it's a little different. We can leverage the error on converting to values (the double hyphens) and run the substitute in that.

=ARRAYFORMULA(
  QUERY(
   IFERROR(
    --IMPORTHTML("https://ipowala.in/ipo-subscription-status/?refresh="&I6,"table",1),
    SUBSTITUTE(
     IMPORTHTML("https://ipowala.in/ipo-subscription-status/?refresh="&I6,"table",1),
    "*","")),
    "select Col1, Col2, Col3, Col4
     where not Col1 = 'IPO Name'
     order by Col1",0))