Mazars – welcome to our Digital Classrooms! We are opening our Digital Classrooms to individual registrations, making our world-leading financial...
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.
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.
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:
A VBA UDF (user defined function) can be used to extract the currency. The sequence for doing this follows:
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
Dim strNumberformat As String
Dim strNumberformatClean As String
‘pick up the numberformat from the Cell property
‘should read something like “”CAD” #,##0.00;”CAD” -#,##0.00″
strNumberformat = FormattedCell.numberformat
‘Clean up the string
strNumberformatClean = Mid(strNumberformat, 2, 3)
‘Alternative (step-by-step) approach for debugging
‘strNumberformatClean = Left(strNumberformat, 4)
‘strNumberformatClean = Right(strNumberformatClean, 3)
‘Return the extracted currency code to the active cell
ExtractCurrency = strNumberformatClean
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:
This website cannot function properly without these cookies.
Analytical cookies help us enhance our website by collecting information on its usage.
We use marketing cookies to increase the relevancy of our advertising campaigns.