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

2 Answers 2

1

You could insert this code as "custom number format" of column A:

[<100]##"c"; [<10000]##"s "##"c"; ##"g "##"s "##"c"

so to obtain something like column B of this picture:

custom

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 Commented Apr 1, 2014 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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