For the sake of this example:
100 copper = 1 silver
100 silver = 1 gold
100 gold = 1 platinum
I have a number that represents number of copper and I'd like to format it such that, in excel, it displays as a number of platinum, gold, silver, and copper. For example, "123456789" would display as "123p 45g 67s 89c". As far as I can tell, formulas by themselves are not an option because I'd like to do some operations on multiple columns that require everything to be in their base-copper representation (e.g. - "Highlight this cell if its contents are 10% higher than this other cell's contents"). As far as I can tell, I can't do this if the contents of a cell are something like "2g 4s", so I want only to display the contents as such.
I suppose one option is to have formulas, where one formula converts the copper value to plat-gold-silver-copper, while the other formula does the inverse? Then, in order to conditionally highlight certain cells, I make use of the inverse?
Something that we might be able to take advantage of is that copper-value numbers can just be split by two-digits each. For example, in 375938457, we know 57 is copper, 84 is silver, 93 is gold, and 375 is platinum. If any of the values are 00, we just omit them.
Is... this something that's too complicated for excel? Feel free to post any supplemental information or explain things in-depth. I'm very new to working with excel. (I'd like to do the same with milliseconds-to-hours/days/months aswell, which isn't as straightforward I think).
#0"p "00"g "00"s "00"c"
Unfortunately, I have only been able to test this in Google Sheets, not in Excel
Essentially, this just takes the formatting #0000000 (which will give you at least 7 digits), and shoves text strings for "p", "g", "s" in the middle, then shoves "c" on the end.
In Excel, but not Google Sheets, you can also have Value Conditions attached to conditional formatting, such as [>=5]"Lots";[>=4]"Many";#0 which would show the numbers 1-5 as "1, 2, 3, Many, Lots"
[>=1000000]#0"p "00"g "00"s "00"c";[>=10000]#0"g "00"s "00"c";[>=100]#0"s "00"c";#0"c"
Unfortunately, you can't use the formatting to make the extra 0 disappear between Platinum and Gold, Gold and Silver, or Silver and Copper.
123456789 will display as 123p 45g 67s 89c, but 246480369 will display as 246p 48g 03s 69c instead of 246p 48g 3s 69c
In Excel you can apply this from the "Number Format" at the bottom when you Right Click on a Cell and Format it (or press Ctrl+1). In Google Sheets, go Format > Number > More Formats > Customer Number Format
Doing this with seconds to hours / days / months is easy:
m" months, "d" days, "H" hours, "M" minutes, and "s" seconds"
Doing it with Milliseconds is not. Official Microsoft documentation about Number Formats can be found here