SOLVED

Leading zeros

Copper Contributor

Good Morning, 

 

I lost my leading 0's on all of my 5 digit product numbers. Is there an If statement that can find anything with a 4 digit number to add a leading 0? 

 

Thanks! 

7 Replies
Hi! Assuming your prduct numbers are in A:A, this will work.

=REPT(0,5-LEN(A9))&A9

It adds repeating zeroes for any missing characters up to 5 characters, then adds that to the front of the same cell.
Thanks! I am still doing something wrong, as this is changing the value of the cell - its calculating a value. I just want to put a 0 in front of the current number. Should I have made them text fields first?
thanks for your help!
best response confirmed by allyreckerman (Microsoft)
Solution
Not sure exactly what you want your end product to look like, do you want it to be all text? Would something like this work? This is unaffected if you convert cell format to text. =IF(LEN(A2)=4,0&A2,A2)

When I try the REPT formula on my end it doesn't change any of the calculations ran off of it.
Hello,
If your data is formatted as text, then DKoontz formula should work.
However, if your data is formatted as a number, then try using a custom number format like "00000" for a 5 digit number with leading zeros.
I'd like to offer one other alternative to DKoontz formula.
I've always been one for simplicity, so I sometime use something like this:
=RIGHT("00000" & A2,5)
This will work for any length number up to 5 digits long. And has the added benefit of formatting the result as text so the leading zeros don't disappear again.
I like this solution

@DKoontz 

 

Thanks! BOTH options worked great!! 

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution
Not sure exactly what you want your end product to look like, do you want it to be all text? Would something like this work? This is unaffected if you convert cell format to text. =IF(LEN(A2)=4,0&A2,A2)

When I try the REPT formula on my end it doesn't change any of the calculations ran off of it.

View solution in original post