Written by Allen Wyatt (last updated October 10, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Got some pesky blank rows in your data that you want to get rid of? This tip provides a wide variety of methods you can ...Discover More
Creating macros can help extend what you can do in Excel. If you work with macros, you know that creating macros from ...Discover More
Do you often need to know the difference between two values in your worksheet? This tip shares a quick little macro that ...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.