CODEWITHSUNDEEP
if-statementgoogle-sheetslogical-operatorsformulas
Ryflex
Ryflex

Reputation: 5779

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)

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.

Re: Rubén's latest code 16/10/2016

I've modified it to =ArrayFormula( IF(NOT(ISBLANK(A2)),

IF(IFERROR(SEARCH("$",A2),0),A2,IF(A2="N/A","N/A",IF(A2="No","No",IF(A2="Alt","Alt",IF(A2="Yes","Yes",
    SUBSTITUTE(
      A2,
      REGEXEXTRACT(A2, "[\£|\€]\d+"),
      TEXT(
        REGEXEXTRACT(A2, "[\£|\€](\d+)")
        *
        VLOOKUP(
          REGEXEXTRACT(A2, "([\£|\€])\d+"),
            {
             {"£";"€"},
               {GoogleFinance("CURRENCY:GBPUSD");GoogleFinance("CURRENCY:EURUSD")}
            },
        2,0),
        "$###,###"
      )
    )
  )))))
,"")
 )

This fixes:

As you can see in the image below the two red cells aren't quite correct as I never thought of this scenario, the second of the two values is staying in it's input form and not being converted to USD.

enter image description here

Upvotes: 1

Views: 2387

Answers (1)

Wicket
Wicket

Reputation: 38286

Direct answer

Try

=ArrayFormula(
  IF(IFERROR(SEARCH("$",A1:A6),0),A1:A6,IF(A1:A6="N/A","N/A",IF(A1:A6="No","No",
    SUBSTITUTE(
      A1:A6,
      REGEXEXTRACT(A1:A6, "[\£|\€]\d+"),
      TEXT(
        REGEXEXTRACT(A1:A6, "[\£|\€](\d+)")
        *
        VLOOKUP(
          REGEXEXTRACT(A1:A6, "([\£|\€])\d+"),
            {
             {"£";"€"},
               {GoogleFinance("CURRENCY:GBPUSD");GoogleFinance("CURRENCY:EURUSD")}
            },
        2,0),
        "$###,###.00"
      )
    )
  )))
 )

Result

+---+------------------+---------------------+
|   |        A         |          B          |
+---+------------------+---------------------+
| 1 | N/A             | N/A                |
| 2 | No               | No                  |
| 3 | Yes £10          | Yes $12.19          |
| 4 | Yes £10 per week | Yes $12.19 per week |
| 5 | Yes €5 (Next)    | Yes $5.49 (Next)    |
+---+------------------+---------------------+

Explanation

OR function

Instead or using OR function, the above formula use nested IF functions.

REGEXTRACT

Instead of using a REGEXEXTRACT function for each currency symbol, a regex OR operator was used. Example

REGEXEXTRACT(A1:A6, "[\£|\€]\d+")

Three regular expressions were used,

  • get currency symbol and the amount [\£|\€]\d+
  • get the amount [\£|\€](\d+)
  • get the currency symbol [(\£|\€])\d+

Currency conversion

Instead of using nested IF to handle currency conversion rates, VLOOKUP and array is used. This could be make easier to maintain the formula assuming that more currencies could be added in the future.

Upvotes: 2

Related Questions