dimanche 16 octobre 2016

Google Sheets Formula to Extract and Convert Currency from € or £ to USD

I'm trying to do the following:

  1. Check the cell for N/A or No; if it has either of these then it should output N/A or No
  2. Check the cell for either £ or or Yes; If it has one of these then it would continue to step 3. If it has $ then it should repeat the same input as the output.
  3. Extract currency from cell using: REGEXEXTRACT(A1, "\$\d+") or REGEXEXTRACT(A1, "\£\d+") (I assume that's the best way)
  4. Convert it to $ USD using GoogleFinance("CURRENCY:EURUSD") or GoogleFinance("CURRENCY:GBPUSD")
  5. Output the original cell but replacing the extracted currency from step 3 with the output from step 4.

Examples: (Original --> Output)

  • N/A --> N/A
  • No --> No
  • Yes £10 --> Yes $12.19
  • Yes £10 per week --> Yes $12.19 per week
  • Yes €5 (Next) --> Yes $5.49 (Next)
  • Yes $5 22 EA --> Yes $5 22 EA

I am unable to get a working IF statement working, I could do this in normal code but can't work it out for spreadsheet formulas.

I've tried modifying @Rubén's answer lots of times to including the N/A as it's not the Sheets error, I also tried the same for making any USD inputs come out as USD (no changes) but I really can't get the hang of IF/OR/AND in Excel/Google Sheets.

=ArrayFormula(
  SUBSTITUTE(
   A1,
   OR(IF(A1="No","No",REGEXEXTRACT(A1, "[\£|\€]\d+")),IF(A1="N/A","N/A",REGEXEXTRACT(A1, "[\£|\€]\d+"))),
   IF(
    A1="No",
    "No",
    TEXT(
     REGEXEXTRACT(A1, "[\£|\€](\d+)")*
     IF(
      "€"=REGEXEXTRACT(A1, "([\£|\€])\d+"),
      GoogleFinance("CURRENCY:EURUSD"),
      GoogleFinance("CURRENCY:GBPUSD")
     ),
   "$###,###"
   ) 
  )
 )
)

The above, I tried to add an OR() before the first IF statement to try and include N/A as an option, in the below I tried it as you can see below in various different ways (replace line 4 with this)

IF(
  OR(
    A1="No",
    "No",
    REGEXEXTRACT(A1, "[\£|\€]\d+");
    A1="No",
    "No",
    REGEXEXTRACT(A1, "[\£|\€]\d+")
  )
)

But that doesn't work either. I thought using ; was a way to separate the OR expressions but apparently not.

Aucun commentaire:

Enregistrer un commentaire