Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

In Google Sheets, I want to decimal-align numbers without adding trailing zeros in the fractional part.

Instead of:  I want:
+------+     +------+
|  56.0|     |  56  |
|  27.0|     |  27  |
|  83.0|     |  83  |
|   2.4|     |   2.4|
|   1.2|     |   1.2|
| 120.0|     | 120  |
+------+     +------+

The format of the first column is easily accomplished (for example, using the format "0.0" or just setting custom decimal points under the 123 button).

In Excel, I believe the format of the second column is accomplished using the format "0.?", but as of this writing it does not work in Google Sheets.

Workaround

It is possible to force trailing characters with Substitute:

= Substitute( Text( formula ; "0.0" ) ; ".0" ; "  ")
= Substitute( Text( formula ; "0.0" ) ; ".0" ; "__" )

Note: the two substituted whitespace characters are not U+0020 SPACE but U+2002 EN SPACE. (U+00A0 NO-BREAK SPACE also works.)

Some undesirable side effects of this workaround are that it

  • modifies not just the display format but also the value of the cell
  • requires the use of a monospaced font in the cell
  • trailing whitespace causes font substitution due to the Unicode and does not print correctly
  • trailing underlines are not as desirable as trailing whitespace
  • confuses maintainers because it is not evident that an EN SPACE is being used
  • (presumably) fails in locales which use the period as the period separator (thousands separator) instead of the decimal marker
share|improve this question

1 Answer 1

I wrote some code to make your live a bit easier, in displaying decimal-aligned numbers.

The first code

can only be used in a range, because it dynamically changes the appearance of the numbers according to the highest decimal place in the range. It also allows you to chose the trailing character, like CHAR(160):

function RANGETRAIL(range,chr){
  chr = chr || '0'; 
  var aValues = new Array(), aDecimals = new Array();

  var max=0;
  for(i in range) {
    if(range[i] != "") {
      var string = range[i].toString();
      aValues.push([string]);
      var aSplit = string.split("."); 

      if(aSplit.length > 1) {
        aDecimals.push([aSplit[1].length]);
        if(aSplit[1].length > max) {
          max = parseInt(aSplit[1].length);        
        } 
      } else {
        aDecimals.push(["-1"]);
      }
    }
  } 

  for(k in aDecimals) {
    if(parseInt(aDecimals[k]) != parseInt(max)) {
      for(var l=parseInt(aDecimals[k]); l<parseInt(max); l++) {        
        aValues[k][0] += chr;
      }
    }
  }     
  return aValues;
}

The second code

is meant to be used for cells. Here you need to set the trailing length:

function CELLTRAIL(cell,len,chr){
  // Brad Christie, http://stackoverflow.com/a/5024108/1536038
  chr = chr || '0'; 
  cell = cell.toString();

  if (!len) return cell;

  var p = cell.indexOf('.');
  p = (p!==-1?(cell.length-p-1):-1);

  for (var m = p; m < len; m++) {
    cell += chr;
  }
  return cell;
}

The third code

will strap a string from all non-numerics, except a "." and ",":

function RTRIM(cell) {   
  return Number(cell.replace(/[^0-9$.,]/g, ''));
}

Example File

Decimal-Aligned Numbers enter image description here


Thoughts

This answer doesn't take away the concerns you mentioned in your question, but it will give some automation (dynamically) in displaying them !! Good luck.

share|improve this answer
    
I'd like to point out that this can lead to strange alignment problems if you aren't using a fixed width font. –  Mir Feb 11 at 3:01
    
@mir thanks for your comment Mir. Did you like the answer I gave? In the thoughts heading I explained that it wouldn't take away the concerns the OP gave. –  Jacob Jan Tuinstra Feb 11 at 8:00
    
The answer was actually really helpful once I realized that I needed to use a fixed width font. I'll be honest - I didn't read the question carefully... I just glanced at it to confirm that it was similar to my issue, then jumped straight to your answer. So I didn't notice that OP included concerns about the fonts. –  Mir Feb 11 at 18:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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