Determining a Format's Currency Symbol

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.

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.)

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.

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.

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.

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.