excel-lambda-CLEANCURRENCYTEXT – Convert currency stored as B or M to a number with just one function

The gist for this lambda function can be found here.

The goal

Sometimes we might have numbers in an Excel file formatted like this:

I’d like to create a function that those values into a number where each value is at the same scale.

A solution

I say “a” solution, because I’m sure there are many others.

Here’s a lambda function called CLEANCURRENCYTEXT:

=LAMBDA(val,[mapping],
  LET(
    _curr,LOWER(LEFT(val,1)),
    _nocurr,SUBSTITUTE(val,_curr,""),
    _nonnumeric,GETNONNUMBERS(_nocurr,FALSE),
    _filtered,FILTER(_nonnumeric,(_nonnumeric<>".")*(_nonnumeric<>",")),
    _joined,TEXTJOIN("",TRUE,_filtered),
    _suffix,IFERROR(_joined,"nope"),
    _defaultmapping,{
                    "b",9;
                    "bn",9;
                    "bns",9;
                    "m",6;
                    "mm",6;
                    "mn",6;
                    "k",3;
                    "nope",0
                    },
    _mapping,IF(ISOMITTED(mapping),_defaultmapping,mapping),
    _multiplier,POWER(
                  10,
                  XLOOKUP(
                    _suffix,
                    INDEX(_mapping,,1),
                    INDEX(_mapping,,2),
                    0
                  )
                ),
    _nosuffix,SUBSTITUTE(_nocurr,_suffix,""),
    _output,_nosuffix*_multiplier,
    _output
    )
)

1. val – this is the value which is currently stored as text and usually has a suffix at the end indicating it’s billions, or millions, or similar
2. [mapping] – this is an optional range or array with two columns where each row has the suffix in the first column and the POWER of 10 that suffix represents in the second column (see below for details)

Here’s what it does:

If you’re interested in how it works, read on.

How it works

To make the function easier to create, we use LET to define variables that step through the calculation.

=LAMBDA(val,[mapping],
  LET(
    _curr,LOWER(LEFT(val,1)),
    _nocurr,SUBSTITUTE(val,_curr,""),
    _nonnumeric,GETNONNUMBERS(_nocurr,FALSE),
    _filtered,FILTER(_nonnumeric,(_nonnumeric<>".")*(_nonnumeric<>",")),
    _joined,TEXTJOIN("",TRUE,_filtered),
    _suffix,IFERROR(_joined,"nope"),

• _curr is extracting the first character of val and converting it to lower case. The assumption here is that the first character is a currency symbol. Converting to lower case makes subsequent steps simpler
• _nocurr is removing the currency symbol from the val
• _nonnumeric uses the GETNONNUMBERS lambda, which returns all the non-numbers from a string into an array where each element contains a single character. The first parameter is the string you want to extract non-numbers from. The second parameter is TRUE if you want the function to return a vertical array, or FALSE for a horizontal array. GETNONNUMBERS uses CHARACTERS. All three lambdas mentioned in this blog post are in the gist which can be found here.
• _filtered is removing any commas or periods from _nonnumeric
• _joined is joining each element of _filtered together into a single string (e.g. if _filtered = {“b”,”n”}, then _joined = “bn”)
• _suffix is providing a default value of “nope” in case _joined is an error

All of the above was really to get at the suffix.

And it’s that complicated because we don’t know whether there will be suffixes of multiple characters or not.

    _defaultmapping,{
                    "b",9;
                    "bn",9;
                    "bns",9;
                    "m",6;
                    "mm",6;
                    "mn",6;
                    "k",3;
                    "nope",0
                    },
    _mapping,IF(ISOMITTED(mapping),_defaultmapping,mapping),
    _multiplier,POWER(
                  10,
                  XLOOKUP(
                    _suffix,
                    INDEX(_mapping,,1),
                    INDEX(_mapping,,2),
                    0
                  )
                ),
    _nosuffix,SUBSTITUTE(_nocurr,_suffix,""),
    _output,_nosuffix*_multiplier,
    _output
    )
)

• _defaultmapping is defining an array of suffix:power pairs as described above. This will be used if no argument has been supplied for [mapping]
• _mapping is either going to be the argument passed to [mapping] or the default array defined above
• _multiplier – here we look for the _suffix in the first column of _mapping and return the value from the second column. So for the text “bn”, we find that in row 2 of the default array, and this call to XLOOKUP returns 9. We pass this number into the second parameter of POWER such that we have POWER(10,9)=1000000000
• _nosuffix is removing the suffix from the _nocurr value. This should now contain only the number which needs to be converted using the multiplier
• Finally, _output simply multiplies _nosuffix by _multiplier

This gives us the required value, and we return it to the spreadsheet, ready and waiting for aggregation or further calculation.

In summary

We learned how to convert currency stored as B or M to a number with just one function.

By using an array of conversion values and the POWER function, we quickly converted text-based currency amounts in different scales to numbers at the same scale.

I hope this is of use to you in some way, either as an example of technique or just using the function as-is.

Share generously!