How to have Excel show numeric values as custom "currencies" (eg. copper, silver, gold)?

Question

I would like to have the following format in a cell in Excel:

123g 99s 99c

100 copper (c) = 1 silver (s)
100 silver = 1 gold (g)

So that, if I enter 12394 it'll be shown as "1g 23s 94c".

Is that possible somehow?

Answer 1

Try this custom format:

#0"g" #0"s" 00"c"

Note that this will show all three "units" regardless of the actual amount, so 12 will be shown as "0g 0s 12c". If you only want to show up to the biggest "unit" with non-zero amount (so 12 would be "12c", 598 would be "5s 98c", etc), you can tweak it as follows, to actually apply different formats based on the value:

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

It's not perfect, for instance 10245 will be shown as "1g 02s 45c", rather than "1g 2s 45c", because it just splits up the cell value rather than dividing it by 100 to get the individual amounts, but other than that it should do what you want.

To handle negative values, you're going to have to combine the above format with conditional formatting, because Excel can only have up to 3 conditions in a single number format. Try the following (I'm assuming Excel 2010, should also work in 2007, not sure about earlier versions):

1. Select the cells and go to "Conditional formatting" → "New rule..." (under the Home tab)
2. Select "Use a formula to determine which cells to format"
3. In the formula box, enter =A1>=0, then click on "Format..."
4. Select the "Number" tab, then "Custom", and enter the number format from above:
   [>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"
5. Click "OK" twice.
6. Repeat steps 1 and 2.
7. In the formula box, enter =A1<0, then click on "Format..."
8. Select the "Number tab, then "Custom", and enter the following number format (basically same as the first one, except handles negative values):
   [<=-10000]-#0"g" #0"s" 00"c";[<=-100]-#0"s" 00"c";#0"c"
9. Click "OK" twice.

If you need to modify or remove the formatting rules, or expand them to apply to more cells, just go to "Conditional formatting" → "Manage rules".

---

Another option is to use a VBA function to format the values. Press Alt+F11 to open the VBA editor, right-click in the Project window and select Insert → Module, then paste the following code (just one of many ways to write such a function, of course):

Function MyFormat(intValue)
    Dim strReturn As String
    If intValue < 0 Then strReturn = "-"
    intValue = Abs(intValue)

    If intValue > 10000 Then
        strReturn = strReturn & Fix(intValue / 10000) & "g"
        intValue = intValue - (Fix(intValue / 10000)) * 10000
    End If

    If intValue > 100 Then
        If Len(strReturn) > 0 Then strReturn = strReturn & " "
        strReturn = strReturn & Fix(intValue / 100) & "s"
        intValue = intValue - (Fix(intValue / 100)) * 100
    End If

    If Len(strReturn) > 0 Then strReturn = strReturn & " "
    strReturn = strReturn & intValue & "c"

    MyFormat = strReturn
End Function

This approach affords you greater flexibility than using custom number formats, and it's easier to use than managing conditional formatting rules - just type =MyFormat(12345) instead of 12345. The downside, however, is that you lose the ability to perform mathematical operations with the data, because Excel now sees the values as text, not numbers.

Answer 2

The following custom format will do what you want:

??\g ??\s ??\c