Google Sheets – Formatting Integer to Gold/Silver/Copper Using Custom Number Format

google sheets

I have number in coppers: there are 100 coppers to a silver and 100 silvers to a gold

I need the raw number to stay in the cell so it can be used in calculations.

Ideal format:

  • 1 ==> 1c
  • 123 => 1s 23c
  • 12045 => 12g 4s 45c
  • 1234507 => 123g 45s 7c

The best custom format I can come up with is:

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

which give this:

  • 1 ==> 0g 00s 01c
  • 123 => 0g 01s 23c
  • 12045 => 12g 04s 45c
  • 1234507 => 123g 45s 07c

This is OK but I wonder if it can be done better?
There are a few undocumented format characters but I don't know what they do

Best Answer

With the following piece of code you can style whatever number you want.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Apply format', 'myFormat').addToUi();
}

function myFormat() {
  var ss = SpreadsheetApp.getActive(), oCol = [];
  var aRange = ss.getActiveRange(), rValues = aRange.getValues();
  for(var i=0, iLen=rValues.length; i<iLen; i++) {
    var oRow = [];  // (Thanks Martin Hawksey)
    for(var j=0, jLen=rValues[0].length; j<jLen; j++) {
      if(typeof(rValues[i][j]) == 'number') {
        oRow.push(getFormat(rValues[i][j]));
      } else {
        oRow.push("");
      }
    }
  oCol.push(oRow);
  }
  aRange.setNumberFormats(oCol);
}

function getFormat(cell) {
  var g = "", s = "", c = "";  
  if(cell >= 0 && cell < 100) {
    c = '00"c"';
  } else if(cell >= 100 && cell < 10000) {
    s = '00"s "', c = '00"c"';
  } else if (cell >= 10000) {
    g = '00"g "', s = '00"s "', c = '00"c"';
  } else { 
    throw 'invalid entry';
  }
  return "#" + g + s + c;
}

Explained

The onOpen() will create a new menu entry. The myFormat will accept the selected reange and spit out the desired format.

Add the script under Tools>Script editor. Press the bug button to authenticate the script and you're on the go !!

Related Topic