I'm trying to follow two ways ...

1. To remove * only ... =QUERY(ARRAYFORMULA(IFERROR(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",)*1,SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",))),"Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))

2. To remove * and replacing "-" by zero simultaneously... =QUERY(ARRAYFORMULA(IFERROR(SUBSTITUTE(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",""),"-","0")*1,SUBSTITUTE(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",""),"-","0"))),"Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))

But result is less data in 2nd case. How to get all data in 2nd case. Consider Col6 data to compare plz. Cell values let's say ..... M5=BAJFINANCE N5=30June2022

CodePudding user response：

use:

=ARRAYFORMULA(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY({REGEXREPLACE(SUBSTITUTE(
 IMPORTHTML("https://niftyinvest.com/option-chain/"&A1&"?expiry="&B1, "table", 1)&"", "*", ), "^-$", "0"), 
 FLATTEN(QUERY(TRANSPOSE(QUERY(REGEXREPLACE(SUBSTITUTE(
 IMPORTHTML("https://niftyinvest.com/option-chain/"&A1&"?expiry="&B1, "table", 1)&"", "*", ), "^-$", "0"), 
 "select Col1,Col2,Col3,Col4,Col5,Col7,Col8,Col9,Col10,Col11")*1),,9^9))}, 
 "select Col1, Col2,Col4,Col5,Col3,Col6,Col9,Col7,Col8,Col10,Col11 
  where Col12 <> '0 0 0 0 0 0 0 0 0 0'", 1)&"♥"),,9^9)), "♥", 1, 0))