by Allen Wyatt
(last updated October 10, 2020)
Jelmer works with a lot of financial data, and the information in a worksheet may be in currencies from many countries. The only way to tell them apart is by the currency symbol used in formatting the cell. For instance, a cell may be formatted to use the currency symbol "EUR" or "SEK." Jelmer wonders if there is a way, in a formula, to determine the currency symbol used to format a given cell.
There is no intrinsic worksheet function in Excel that will return what you are seeking, so it is not possible to do in a formula. You can, however, create your own user-defined function that will return the desired information. Here's an example:
Function GetCurrency(rCell As Range) As String Dim sTemp As String Dim sKeep As String Dim sThrowAway As String Dim J As Integer Application.Volatile sKeep = "Not a Numeric Value" If IsNumeric(rCell) Then sThrowAway = "0123456789.,()+- ]" sTemp = rCell.Text sKeep = "" For J = 1 To Len(sTemp) If InStr(sThrowAway, Mid(sTemp, J, 1)) = 0 Then sKeep = sKeep & Mid(sTemp, J, 1) End If Next J End If GetCurrency = sKeep End Function
In order to use the function, you would use the following, assuming you wanted the currency symbol for whatever is in cell B7:
This works because the .Text property for a cell returns the formatted version of whatever is in the cell. Assuming the cell contains a numeric value (the function tests for that), then the formatted text is assigned to the sTemp variable. Then, each character in that text is compared against whatever is in the sThrowAway string. If there is a match, then the character is ignored, otherwise it is added to the sKeep variable. This is what is returned by the function.
Note, as well, that if the cell being evaluated does not contain a numeric value, then an error message ("Not a Numeric Value") is returned.
There one gotcha to be aware of with the function: If you change the formatting of a target cell, that doesn't trigger the macro. This seems a shortcoming of Excel itself—it doesn't trigger a recalculation of the worksheet when you change a format, so the macro itself isn't triggered. It will, however, trigger the next time you make a change to the worksheet that does requires recalculation.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13787) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
A common task for macros is to open and process a file you want imported into your workbook. If you need to identify the ...Discover More
Do you need to create a number of words or phrases where you only alter a few letters in each one? If the alterations ...Discover More
Disabling function keys is rather easy to do when you rely on the OnKey method in a macro. This tip looks at how you can ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.