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:
=GetCurrency(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.
Note:
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 MoreUsing a macro to add worksheets to your workbook is easy. This tip provides two different methods you can use.
Discover MoreGot a workbook cluttered with all sorts of macros? Delete them and you'll make your workbook easier to manage.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-10-11 04:15:09
Roy
Sorry, the "R{-5]C[-4]" makes more sense if you know I was in cell D4 then, not in the A1 at the start of that. Easier to spot that kind of thing re-reading it as a whole insted of in a small(-ish) box like this, but still, should've caught it.
2020-10-11 04:11:54
Roy
OK, reference was fine, just got off GET.CELL into a different thing so the 53 I was finding was not for it...
Looked through a few other things though, as I do each time I look something up and found "RELREF" which seemed pointless before as OFFSET() works smoothly cell-side. And it had weird examples that made little sense to me. Decided to work out what they meant and it turns out you can give the function a pattern for it to suss out the offsets with and feed that to other functions. Or use it to read a cell, of course.
Here's the interesting part, and I wish I'd worked on figuring out what the examples meant a long time ago, 'cause I might've used it once or twice over the years, but it'd've been interesting to know etiher way. Not, you know, because I would have used it endlessly like GET.CELL(41,reference), but because I like interesting things:
It is like OFFSET() except it rolls around. Like in Asteroids (I'm old). So where OFFSET(A1,-1,-1) yields a #REF! error, RELREF does NOT. it would roll left and up to the bottom right of the spreadsheet and give you the address R{-5]C[-4] and combined with, say, ABSREF like in the examples, will give you cell XFD1048576's contents. OFFSET() just won't do that. It hits top and left and any more causes the error.
Wonder how many others of them that I ignored because of nice "regular" functions are not limited in the little ways "regular" functions so often are. Like CELL("width",A1)... it will tell you an INTEGER width, but not the true width. GET.CELL(16,A1) will tell you the "0.00" width of the cell/column. Curious, those little details. (Learned that one a while back due to a Tip here concerning getting an average that did not include hidden columns, trying to find something in these functions that would overcome that limitation, hoped, but did not.)
2020-10-11 01:02:05
Roy
Yep, there it is, though I prefer the more interesting thought I just commented about!
A different reference for the functions show the "53" parameter is supposed to do:
Contents of the cell as it is currently displayed, as text, including any additional numbers or symbols resulting from the cell's formatting.
and must be the correct version since that is exactly what Fred Potter's Named Range does.
Ah well, I did like the imaginary world I supposed last comment... but reality is reality.
2020-10-10 21:08:21
Roy
Not gonna claim I understand why Fred Potter's formula works as "53" is supposed to be this:
53
A number indicating the orientation: 1 = Portrait 2 = Landscape
but it DOES return the entire string, number value included, not just the formatting. So the RIGHT() it is wrapped in will give the entire value to parse, number content included, rather than just the format itself, like GET.CELL(7,reference) does.
Perhaps my function guide is incorrect about what "53" does, but I'm suspecting something more along the lines of if a function has nonsense (from the POV of what it is supposed to be doing, not because you entered nonsense) to work on, perhaps it returns a full, formatted string of whatever is in the cell reference to the cell the formula, and lets Excel continue the operations in the formula as best it can. That would mean that since it has a cell reference, not a page reference (for example, in a macro generating a printout — remember, these are all meant to be used as steps in macros, not as formula functions), it considers the command nonsense but rather than fail, returns a full string, formatted, to Excel for Excel to use if it can. Then in Fred Potter's Named Range, that string is taken apart by RIGHT() finding the last three characters and outputting them.
That would be INTERESTING and a potentially useful way to obtain a fully formatted string when other formulaic methods might not be able to do so. The gift that keeps on giving!
Wonder how that would interact with Precision As Displayed?
Something new to learn each day with Excel. Wonder if Photoshop users feel like this? Probably, I guess! I hate MS, but do like this aspect of Excel.
2020-10-10 20:48:30
Roy
If you don't wish to use a macro, an alternative exists.
It DOES have the same "gotcha" as the UDF: changing a format won't fire it so you must make a change somewhere, or just directly press F9 to ensure everything is up-to-date.
It requires you to use a Named Range ("NR"), which I will call "GetCurrency" to parallel the Tip's solution. It requires the NR because it uses a command from the Excel 4 Macro set and those must be used in Named Ranges to work. They will NOT NOT NOT work in a regular cell formula. No doubt, the "macro" aspect of them subjects them to the same "gotcha" mentioned, that of the not automatically updating.
Two things to think about when creating the NR:
1) The referencing. This will not take any parameter, like a UDF can (More on how to do so is a very different conversation, but you CAN use NR's that take parameters and the recent addition of LET() makes it a fairly simple thing to do. "Fairly" simple.), so if you want it to work on the cell to the left of where the formula using it will be, select such a cell and relatively reference the cell to its left in the formula. If you need it to operate on only a single cell (so, not, say, a column of things to operate on, but rather a single output cell in a screen report), then absolutely reference that cell.
2) If you set up the formula with JUST the formula to do the work, then it will only update when selecting that cell, pressing F2, then Enter. If you instead add something volatile to the NR formula (NOT to the cell-side formula using the NR!!!) then pressing F9 or making any other change will cause any cell using the NR to update. Use such a volatile bit in the cell-side formula and that just won't happen.
So, the following:
NR is "GetCurrency"
Its basic formula is:
=GET.CELL(7,Sheet1!$A$1)&IF(TODAY()=TODAY(),"","")
or
=GET.CELL(7,Sheet1!A1)&IF(TODAY()=TODAY(),"","")
It is going to report the whole formatting string so might return something like:
[$TRL] #,##0.00 or #,##0.00 U\S\D
So you can either, in the NR formula OR in the cell formula (or divide it between both), strip out the undesirable parts. Perhaps nest in in SUBSTITUTE()'s to remove any and all "$", "[", "]", "#", ",", "0", ".", "\", " ", and "?" characters in the NR, figuring those would potentially always exist, and use the cell-side formula to strip out anything else objectionable that cropped up over time. Or better, name the above formula "Format" and the second NR, "GetCurrency" might be the "stripper" so the output to cell-side is in the nicely named NR.
Side notes here, learned a couple new things about custom formatting writing this. One is that the "U" character does not require the "\" before it like most letters do. A second is that if you place anything inside "[$xxxxxx]" you don't need the "\" for the text. So "[$USD}#,##.00" gives "USD34.55" as an output and I'd always thought I had to use "\U\S\D#,##0.00" to achieve that (Didn't know the "U" didn't need the "\" before today either!) So using that there would be no "\" 's to strip off, but then your sources of input might not know the above either so... And a third is that characters usually assigned a role in formatting will perform that role in text, unless "escaped" with that backslash. So "Format" on the value "12" will give you "For1at" in the cell because the "m" is returning the month number (12 is 01/12/1900 as a date so the month number is 1). I'd never actually tried shooting for a word to be the displayed value for one's entry before either, but today seems a day for interesting thoughts. Hmm... the above assumption that one could "escape" the "m" with a backslash seems to not work... something else to pursue, but a side note to these side notes so... Ahh, no, as a direct custom format, escaping the "m" with "\" DOES work. It's a limitation on the format string in TEXT(), and that's a known area of limitation.
Well, where was I? Ah yes, so with the stripping, we are already doing pretty much what the UDF does. Of course, the UDF can do MUCH, MUCH MORE and MORE EASILY but then you have a macro workbook to use (no problem, to you, but if you need to distribute it...). Which brings up the last thing to mention:
When saving the workbook, Excel will tell you you have to save it as a macro (.xlsm) workbook or lose functionality. This is NOT NOT NOT true. Just save it normally (.xlsx) and this will all still be available next time you open it. And it will not ask again when saving unless you modify things. (Well, I imagine individual versions of Excel might differ about the asking each time thing but I have no way to test that.)
Gotta love about a dozen of those old Excel 4 macro commands. And that Excel, according to hints in some MS support pages, might not EVER be able to do away with them because some aspects of them, or more likely, things underpinning them, seem essential to some of Excel's current functionality so all the warnings written for 25 years might be unneeded. Yeah, 25 years and still going strong. Lord, you could, if for some STRANGE reason you wanted to, even enable their full macro use and write the old macros themselves instead of VBA ones, or alongside VBA ones. Made sense in 1997, when everyone still knew how, but if you had to learn a macro language to use now, why would you ever learn them instead of VBA??? Still, they are the gift that keeps on giving.
2020-10-10 17:44:00
Fred Potter
IF the cell is custom formatted like #,##0.00 "EUR", here's a non-VBA solution using an old Excel4 macro function, from Fazza MrExcel MVP 2009.
It's created as a function in a defined name and then used in an adjacent cell. So, it has to be set up to work relative to the cell where you want the answer.
If your data is in cell A3, make cell B3 active and then create a defined name (CTRL-F3 shortcut) called "C_C" and in refers to,
enter =RIGHT(GET.CELL(53,A3),3).
After creating the defined name, go to any of your custom formatted cells and in the cell on its right enter the defined name (F3) in the cell, that is, =C_C
This should show you the desired currency.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments