0

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.

  • can you share a copy of your sheet? – user0 Aug 2 '18 at 1:33
  • no I can't. But if you just open up a new sheet and type 20150613 then try your method, you would see 72 – jason Aug 2 '18 at 1:56
1

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!!!

  • 1
    wow. that's insane. I rather just use the right function then. – jason Aug 2 '18 at 4:11
  • @jason You could, "but I don't want to do that, I need the number to stay as is... just change how it is displayed.".. Especially-this won't change the underlying number – TheMaster Aug 2 '18 at 4:36
  • @jason So,Did you try? – TheMaster Aug 2 '18 at 15:40
  • 1
    no, i'm sure it works. I'm just going to use the right function for now, try it later. – jason Aug 2 '18 at 20:57
-1

This would be possible without script only if your 8 digit number is a date. Then you can custom format it as MMDD

Otherwise, the best you can get is to use +1 column with =RIGHT(A1; 4) and make it visual one while you will be still referring to initial column which will be hidden or in another sheet.

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.