Use VBA to extract the Currency from a Custom Number Format in Excel

When exporting data from information systems (accounting, sales, product databases, etc) you may face the risk of the export including custom Excel number formats. Typically, this is not a problem as you can simply change them to whatever you prefer but there are exceptions.

Exported data may contact the currency in a custom Excel format

In the example below, the data export contains the product value stated as a number, with the currency quoted only in the form of a custom format in Excel. For each currency a different Custom Number Format has been used. You can see how Product A is quoted in CAD, and Product B is quoted in GBP for example.

Our mission here is to extract the currency code (CAD / GBP etc) into a separate column (CUR) which will then drive the lookup column “FX” for a final number in GBP (or a currency of your choice as the end output.

Option for extracting the currency code from the custom number format

The most commonly quoted option for extraction of the number format from an Excel cell is the CELL-function, but it has clear limitation when it comes to manually (or system-exported) custom number formats which use different currencies for different cells.

Applying the CELL format to our custom format, we would simply get “,2” as a result, which does not in any way help us identify which currency has been used.

For further reading on Excel custom number formats, this article has some additional perspectives:
https://financialmode1.wpengine.com/how-to-speed-up-your-models-part-3/

VBA function to extract the currency from a custom format Excel

A VBA UDF (user defined function) can be used to extract the currency. The sequence for doing this follows:

1. Extract the text string representing the full custom number format using the [cell].numberformat property (gives something like “CAD” #,##0.00;”CAD” -#,##0.00″)
2. Trim the text string  (MID) to remove the everything except the currency code
3. Return the currency code as a string to the spreadsheet

If you are unfamiliar with VBA UDFs, Microsoft provides an introduction here, and if you are serious about learning VBA you may want to consider the Mazars VBA course which is one of many financial modelling courses that we offer.

Of course, if you prefer, you could have done the FX lookup in VBA too, but for transparency reasons we recommend minimising the VBA logic and leave most of the logic in Excel.

Function ExtractCurrency(FormattedCell As Range) As String

‘This function extracts the number format from Currency formatted cells (CAD/GBP/USD, etc)

‘Useful in the specific situation where DIFFERENT formatting codes have been used in a range

Completing the VBA currency conversion exercise with a lookup (XLOOKUP) table

Once you have extracted the currency code, things are pretty straight forward. The easiest (and most modern) way is to use and XLOOKUP in a data table, but of course if you prefer the old INDEX/MATCH then that would also work a treat. I’d recommend being careful with LOOKUP as it requires data to be sorted, and future additions (maybe by someone else) to the currency table may not be added correctly (sorted) which will introduce calculation errors.

For more background on LOOKUP vs VLOOKUP / HLOOKUP this article gives some pointers:
https://financialmode1.wpengine.com/resources/use-lookup-to-replace-vlookup-and-hlookup-functions-in-excel/