Forum Discussion
If conditional multiplication
Depending upon the situation, one possible solution is to write a named Lambda function
ExtractCurrencyFormatλ = LAMBDA(ref, LET( rawFormat, GET.CELL(7, ref), TEXTBEFORE(TEXTAFTER(rawFormat, "["), "]") ) )The worksheet formula would be
= ExtractCurrencyFormatλ(currencyAmount) for a single value, or = MAP(currencyAmount, ExtractCurrencyFormatλ) for an array.Reference:
How to find the currency format in a formula | MrExcel Message Board
Does the "£" show along with the value in the formula bar or is it added through cell formatting?
You could use the CELL function to identify the cell format.
=CELL("format",A1)If £150 is A1, for example, the above returns ",2"
if $150 is A1, the above would return "C2"
Why you might not want to use CELL:
- it's slow
- it doesn't spill
and this:
Note: If the info_type argument in the CELL function is "format" and you later apply a different format to the referenced cell, you must recalculate the worksheet (press F9) to update the results of the CELL function.
I;d use a separate column for the currency. You can then use a simple INDEX/MATCH formula instead of making Excel jump through hoops to determine the number format.
See the attached demo.
