Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
1  
Can you give more details here? What different types of input are you expecting? Will the numbers always be formatted xxxx - xxxx - xxxx? If you could give an example of what a user enters, that would help. –  OnenOnlyWalter Dec 11 '12 at 20:25
    
@OnenOnlyWalter They may be entering the number as 123456789012, 1234-5678-9012, 1234 5679 9012, etc... Basically I want to alter it to the correct format. (spacing.) –  VuoriLiikaluoma Dec 12 '12 at 5:30
    
Did the script work? –  Jacob Jan Tuinstra Feb 7 '13 at 20:58
    
@JacobJanTuinstra Haven't tried it yet, but I did save a copy of that example spreadsheet you made. –  VuoriLiikaluoma Feb 8 '13 at 5:19
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for this, will see if I can implement it into my spreadsheet. –  VuoriLiikaluoma Feb 7 '13 at 12:04
    
In your spreadsheet, goto tools/script editor. Then paste the complete code. Save and you're on the go! –  Jacob Jan Tuinstra Feb 7 '13 at 12:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.