Skip to content

Instantly share code, notes, and snippets.

@ncalm
Last active June 15, 2023 14:15
Show Gist options
  • Save ncalm/e61010a57fdc2f466c6b64bd8fdc10ce to your computer and use it in GitHub Desktop.
Save ncalm/e61010a57fdc2f466c6b64bd8fdc10ce to your computer and use it in GitHub Desktop.
This collection of Excel lambda functions will quickly convert currency values stored as text with a scaling suffix to a numerical representation of the same number in a standard scale
/*
CLEANCURRENCYTEXT
This lambda function will quickly convert currency values stored as text
with a scaling suffix such as "B" (for billions) or "M" (for millions)
and so on, to a number all in the same scale
inputs:
- val, a single value to be converted as described above
- [mapping], a two-column array or range of suffix:power pairs such as
{"b",9} (see _defaultarray for more examples)
returns:
a single value where, for example, $180B is converted to 180000000000
*/
CLEANCURRENCYTEXT =LAMBDA(val,[mapping],
IF(ISNUMBER(val),val,
LET(
_curr,LOWER(LEFT(val,1)),
_nocurr,SUBSTITUTE(val,_curr,""),
_chars,MID(_nocurr,SEQUENCE(LEN(_nocurr)),1),
_nonnumeric,FILTER(_chars,ISERR(INT(_chars))),
_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,
_test,HSTACK(_mapping,_multiplier,_nosuffix,_output),
_output
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment