2

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

1

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 !!

1
  • 1
    Thanks, this works very well for static cell values but needs updating manually if the values are calculated from a formula Apr 1 '14 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.