0

I use this formula to get the data:

importXML("https://at.fuelo.net/fuel/type/gasoline?lang=en"; "//table[@class=('table')]//tr")

How to get price without a sign €/l?

player0
  • 124,011
  • 12
  • 67
  • 124
Tomash Prudków
  • 33
  • 5

2 Answers2

2

Use substitute() or regexreplace() with iferror(), like this:

=arrayformula( 
  iferror( 
    iferror( 
      0.001 *
      value( 
        substitute( 
          importxml("https://at.fuelo.net/fuel/type/gasoline?lang=en", "//table[@class=('table')]//tr"), 
          " €/l", "" 
        ) 
      ), 
      0.001 * 
      importxml("https://at.fuelo.net/fuel/type/gasoline?lang=en", "//table[@class=('table')]//tr") 
    ), 
    importxml("https://at.fuelo.net/fuel/type/gasoline?lang=en", "//table[@class=('table')]//tr") 
  ) 
)

Or, depending on your locale, it may be:

=arrayformula( 
  iferror( 
    iferror( 
      0,001 *
      value( 
        substitute( 
          importxml("https://at.fuelo.net/fuel/type/gasoline?lang=en"; "//table[@class=('table')]//tr"); 
          " €/l"; "" 
        ) 
      ); 
      0,001 * 
      importxml("https://at.fuelo.net/fuel/type/gasoline?lang=en"; "//table[@class=('table')]//tr") 
    ); 
    importxml("https://at.fuelo.net/fuel/type/gasoline?lang=en"; "//table[@class=('table')]//tr") 
  ) 
)

The 0.001 * bit and the outer iferror() are only required when your spreadsheet locale uses period as decimal mark.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • Please indicate which **File > Settings > Locale** your spreadsheet is using. Consider sharing a publicly editable [sample spreadsheet](https://webapps.stackexchange.com/a/138383/269219) with _realistic-looking_ data, and showing your _manually entered_ desired results there. – doubleunary May 15 '22 at 21:19
0

use:

=QUERY({IMPORTXML("https://at.fuelo.net/fuel/type/gasoline?lang=en"; 
 "//table[@class=('table')]//tr")\ INDEX(SUBSTITUTE(IMPORTXML(
 "https://at.fuelo.net/fuel/type/gasoline?lang=en"; 
 "//table[@class=('table')]//tr"); " €/l"; "");;2)}; 
 "select Col1,Col4,Col3")

enter image description here

update:

=ARRAYFORMULA(IFNA(TEXT(REGEXEXTRACT(SUBSTITUTE(
 IMPORTXML("https://at.fuelo.net/fuel/type/gasoline?lang=en"; 
 "//table[@class=('table')]//tr"); "."; ","); 
 "\d+,\d+")*GOOGLEFINANCE("currency:EURUSD"); "0.00"); TRIM(
 IMPORTXML("https://at.fuelo.net/fuel/type/gasoline?lang=en"; 
 "//table[@class=('table')]//tr"))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Please tell me how you can hide a "Difference" column of data? – Tomash Prudków May 15 '22 at 17:57
  • @TomashPrudków use: `=ARRAYFORMULA(ARRAY_CONSTRAIN(IFNA(TEXT(REGEXEXTRACT(SUBSTITUTE( IMPORTXML("https://at.fuelo.net/fuel/type/gasoline?lang=en"; "//table[@class=('table')]//tr"); "."; ","); "\d+,\d+")*GOOGLEFINANCE("currency:EURUSD"); "0.00"); TRIM( IMPORTXML("https://at.fuelo.net/fuel/type/gasoline?lang=en"; "//table[@class=('table')]//tr"))); 9^9; 2))` – player0 May 15 '22 at 21:33