Google Sheets Formula to Extract and Convert Currency from € or £ to USD
<p>I'm trying to do the following:</p>

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

<h3>Examples: (<strong>Original --> Output</strong>)</h3>

<ul>
<li><code>N/A</code> --> <code>N/A</code> </li>
<li><code>No</code> --> <code>No</code> </li>
<li><code>Alt</code> --> <code>Alt</code> </li>
<li><code>Yes</code> --> <code>Yes</code> </li>
<li><code>Yes £10</code> --> <code>Yes $12.19</code></li>
<li><code>Yes £10 per week</code> --> <code>Yes $12.19 per week</code></li>
<li><code>Yes €5 (Next)</code> --> <code>Yes $5.49 (Next)</code></li>
<li><code>Yes $5 22 EA</code> --> <code>Yes $5 22 EA</code></li>
<li><code>Yes £5 - £10</code> --> <code>Yes $5.49 - $12.19</code></li>
</ul>

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

<p>I've tried modifying @Rubén's answer lots of times to including the <code>N/A</code> 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.</p>

<pre><code>=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")
     ),
   "$###,###"
   ) 
  )
 )
)
</code></pre>

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

<pre><code>IF(
  OR(
    A1="No",
    "No",
    REGEXEXTRACT(A1, "[\£|\€]\d+");
    A1="No",
    "No",
    REGEXEXTRACT(A1, "[\£|\€]\d+")
  )
)
</code></pre>

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

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

<p>I've modified it to =ArrayFormula(
IF(NOT(ISBLANK(A2)),</p>

<pre><code>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),
        "$###,###"
      )
    )
  )))))
,"")
 )
</code></pre>

<p>This fixes:</p>

<ul>
<li>Blank cells no longer throw #N/A</li>
<li><code>Yes</code> only cells no longer throw #N/A</li>
<li>Added another text value <code>Alt</code></li>
<li>Changes the format of the currency to 0 decimal places rather than my original request of 2 decimal places.</li>
</ul>

<p>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.</p>

<p><a href="https://i.stack.imgur.com/yQLUV.png" rel="nofollow"><img src="https://i.stack.imgur.com/yQLUV.png" alt="enter image description here"></a></p>