Google Sheets – Text Cell Formatting Question

google sheetsgoogle-apps-script

I'm trying to create some sort of automated formatting for certain columns (except headers of course.) to take a string of numbers (and previous formatting; which should be stripped.) and format it as

1234 – 5678 – 9012

for example. Now sure I do that manually now, but once other people start using the spreadsheet it's going to look ugly unless they make a small effort to keep the format.

Is this possible?

Best Answer

I've created this little script to format the input:

function SCHANGE(array) {
  if(typeof(array) == 'number') {
    var number = array.toString();
  } else {
    var number = array.replace(/\D/g,'');
  }

  if(number.length != 12) {
    return "<> 12"
  } else {
    var first = number.substring(0,4);
    var mid = number.substring(4,8);
    var last = number.substring(8,12);
    var formText = first + " - " + mid + " - " + last;
  }
  return formText;
}

The script validates the input and converts it, checks whether 12 digits are given and returns the outcome. I've prepared an example file for you to play around with: formatted text.

Related Topic