Search code examples
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-queryimportrange

Importrange Function with Condition to remove Special Character

I have a Google sheet Data table with data which looks like this 

<0.03
<0.03
<0.03
<0.03
<0.03
<0.03
0.04
0.03
<0.03
<0.03
<0.03

I want to import this data into another google sheet but also remove the special character <

Expected result is 

0.03
0.03
0.03
0.03
0.03
0.03
0.04
0.03
0.03
0.03
0.03

if the Importrange formula goes by 

=importrange("https://docs.google.com/spreadsheets/d/1RstSwZGiTzKDdEy8D0zxz2sczr1Bnf4_0-CbpM_Po0/edit?ts=5ccaa041#gid=xxxxxxxx","Sheet1!D4:D")

What conditional filter should I use to remove the < special character? I understand that I need to use a Query Function with the importrange. But not sure on the condition to use to remove the < special character.

Solution 
  • =ARRAYFORMULA(IF(LEN(IFERROR(REGEXEXTRACT(TO_TEXT( 
 IMPORTRANGE("ID", "Sheet2!A1:A")), "\d+.*"))), VALUE(IFERROR(REGEXEXTRACT(TO_TEXT( 
 IMPORTRANGE("ID", "Sheet2!A1:A")), "\d+.*"))),))

    0