Google Sheets – Display Only Last 4 Digits

formulasgoogle sheetsgoogle-apps-scripttext formatting

I have an eight-digit number (20150613), I just want to display the last 4 digits like 0613. I know I can use a formula like =right(20150613,4), but I don't want to do that; I need the number to stay as is, just change how it is displayed.

Best Answer

You'll need scripts. Try this on a "copy" of your sheet: 

function Camouflage(){
  var sh = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var rng = sh.getRange("A1:A"+sh.getLastRow());
  rng.setNumberFormat("@");
  var val = rng.getValues();
  var rtfArr = val.map(function white(e){
    if (e.map){
      return e.map(white);
    }else{
      var rich = SpreadsheetApp.newRichTextValue(); //new RichText
      rich.setText(e); //Set Text value in e to RichText as base 
      var style = SpreadsheetApp.newTextStyle().setForegroundColor("#ffffff"); // Create a new text style with white foreground
      var buildStyle = style.build(); 
      rich.setTextStyle(0,4,buildStyle); // set this text style to the first four characters
      var rtf = rich.build();
      return rtf;
    }
  });
  rng.setRichTextValues(rtfArr);
  rng.setNumberFormat("0");
}

Modify,Debug and learn!!!

Related Topic