Excel, create a view that turns a number into gold, silver, and copper

format your cell on this custom #0"g" #0"s" 00"c"

I think that is some of what I tried to do.  A part of it works.  For instance the cell now has "6698.65" in it.  After formatting it like that it shows "0g 66s 99c".

Any other thoughts please?

can you use this custom format then

[>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"

With that one "6698.65" shows as "66s 99c".

then for this amount 6698.65  how exactly it should show?  so that i make the format for you

It should show "66g 98s 54c".

More examples:
4019.9 = 40g 19s 9c
.48 = 48c
1.79 = 1s 79c

I hope that helps, and thanks!

in this case, you cannot achieve this by using custom format for this one you need somewhat a sophisticated formula like this one.

put the amount in cell A1 and then put the below formula in B2 and then if there are many cells then just copy formula down.

=CONCATENATE(FLOOR((A1-(FLOOR(A1/10000, 1)*10000))/100, 1),"g ",INT(MOD(A1, 100)),"s ",INT(MOD(A1,1)*100),"c ")

Select allOpen in new window

plz see attached workbook example
Book1.xlsx

Getting real close.  Seems like Excel would be able to expand your initial thought to allow more digits, would be so much easier.  

If I put 5459.5 (54g 59s 5c) it will calculate 54g 59s 50c.  Note it shows 50c instead of 5 copper.

I guess I am coming to realize as well that I might should just stick with using what I had been using, but it would just be so much better showing the values like your coming up with.

Ah I see now.  I can just put 531.02 for 2 copper.  I can just put 531.2 for 20 copper.  That can work.

try with this one. it will fix the above mentioned trailing zero issue

use either of the formulas, the previous one i provided or this one.

i think the above one , gives you more precise detail. but depends on what you want to do with it.

=CONCATENATE(FLOOR((A1-(FLOOR(A1/10000, 1)*10000))/100, 1),"g ",INT(MOD(A1, 100)),"s ",SUBSTITUTE(TRIM(SUBSTITUTE(INT(MOD(A1,1)*100),"0"," "))," ","0"),"c ")

Select allOpen in new window

Yeah, I'll use your first one.  The second one doesn't like when I put in 50c, since Excel makes .50 to 5 by default.  Plus you said it more precise.  

So now my sheet, will have a bunch of related cells for the formula hey?  Here is my sheet for reference.
Cost-of-Damask-if-to-make-money.xlsx

ok i have modified the formula a bit to become more precise.

attached is the version with my first formula.
Cost-of-Damask-if-to-make-money.xlsx

Got it.  I noticed that if I put more than 1000 it doesn't show correctly. If I put 13,209.09 it comes out to be :32g 9s 1c.

You have helped with what I have wanted for sure.  In the end I can't see how I can keep a clean spreadsheet and have it easy enough to format the numbers like so.  If I want to make a list of materials and calculate those I have to basically have two columns, one with the excel way, then another column with the formula.  

I have been playing with is some more, even with trying to put the formulated cells in another sheet.  Its getting to be too much work to do just a little bit of what I want to do.  

I am accepting the solution and thanks for all of your help.

User was timely and did just what I needed.