How to add LEADING zero's to a number?


I want to convert every number to a 3 digit number - eg. 1 has to be 001 and 15 would be 015. In excel TEXT would have helped. whats the equivalent here?



You can use the formula REPT and LEN functions and CONCATENATE the result of REPT and LEN to your data, as shown below -

CONCATENATE(REPT( 0 , 3 -LEN( "15")) , "15")
Where 15, is your data

  • LEN(“15”) gets the total number of characters within double qoutes " ",
  • REPT(0, 3-LEN(“15”)) adds the required number of zeros. To calculate how many zeros should be added, you subtract the length of the string in " " (here, “15”) from the maximum number of zeros (here, 3).
  • Finally, you concatenate zeros with your data (here, “15”).

Hope this helps.



Thanks Chirag.

Just a small update - if you want to run this on a number, which currently isnt accepted in LEN, you can wrap number in concatenate before passing to LEN. Eg. LEN(CONCATENATE(number,""))