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?

# How to add LEADING zero's to a number?

**Chirag**#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

**jinen**#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