I'm trying to do the following:
- Check the cell for
N/AorNo; if it has either of these then it should outputN/AorNo - Check the cell for either
£or€orYes; 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. - Extract currency from cell using:
REGEXEXTRACT(A1, "\$\d+")orREGEXEXTRACT(A1, "\£\d+")(I assume that's the best way) - Convert it to $ USD using
GoogleFinance("CURRENCY:EURUSD")orGoogleFinance("CURRENCY:GBPUSD") - Output the original cell but replacing the extracted currency from step 3 with the output from step 4.
Examples: (Original --> Output)
N/A-->N/ANo-->NoYes £10-->Yes $12.19Yes £10 per week-->Yes $12.19 per weekYes €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