How do I decimal-align numbers in Google Sheets

Question

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

Answer 1

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

result

Example

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

Answer 2

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

---

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.

Answer 3

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");
      }
    }
  }
}