13

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
5

With the following custom number format, you are able to decimal-align numbers in Google Sheets, even by using fonts that use kerning.

Custom format

???.?????

Screenshot

custom number format
enter image description here

result
enter image description here

Example

I've added this solution in an example file: Decimal-Aligned Numbers

  • 1
    Support for the "?" format character was absent when I originally asked the question. I wonder if it was added as part of Google's recent support of accounting number formats. This is a useful workaround, very close to what I want. The only limitation is that a decimal point appears even when there is no fractional part. In Excel, that limitation can be worked around with conditional formatting. But not, it seems, in Sheets. I am accepting this answer. – MetaEd Mar 7 '17 at 16:23
4
+100

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.

  • 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 '14 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 '14 at 8:00
  • 1
    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 '14 at 18:01
1

You can set the format conditionally by using an Apps Script to iterate over the range of cells and apply different Custom Number Formats based on the value of the cell (integer vs non-integer). Using Custom Number Formats, you can display white spaces of varying width using the underscore character, for example, _. inserts white space the width of a period and _0 inserts whitespace the width of a zero character. Incidentally, most fonts use the same width for all numeral characters so a 0 character is the same width as a 1 and 2 and 3 etc.

This is an example of how the numbers are displayed before and after applying the formatd by running the script. One drawback is that a change in the value of the cell from integer to non-integer or vice-versa will require the formatting to be re-applied to show or hide the decimal as needed.

  Raw Numbers  Decimal Aligned
+------------+----------------+
|    1234.777|         1,234.8|
|        5454|         5,454  |
|           7|             7  |
|         0.5|             0.5|
+------------+----------------+

Script

function decimalAlign() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange("B2:B5");
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var currentCell = range.getCell(i,j);
      var currentValue = currentCell.getValue();
      if (Math.round(currentValue) == currentValue) {
        currentCell.setNumberFormat("#,##0_._0");
      } else {
        currentCell.setNumberFormat("#,##0.0");
      }
    }
  }
}

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.