How to add LEADING zero's to a number?

#1

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?

0 Likes

#2

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.

0 Likes

#3

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,""))

0 Likes