How to remove * from importhtml output data in google sheet-CodePudding

I need to remove * in following data output ....

=IMPORTHTML("https://niftyinvest.com/option-chain/BAJFINANCE?expiry=26MAY2022","Table",1)

I want to know the formula to do it.

CodePudding user response：

Try

=arrayformula(iferror(
substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&A1&"/strike/"&B1&"?expiry="&C1,"Table",1),"*","")*1,
substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&A1&"/strike/"&B1&"?expiry="&C1,"Table",1),"*","")))

CodePudding user response：

or try just:

=ARRAYFORMULA(SUBSTITUTE(IMPORTHTML(
 "https://niftyinvest.com/option-chain/"&A2&"/strike/"&B2&"?expiry="&C2, 
 "table", 1), "*", ))

to not import stuff twice and save resources. yes the output is text but if you intend further calculations with those numbers, they will be auto-converted to numeric values