Left Pad a Number with a Zero if the Length is Odd in Excel

Whenever you open a CSV file in Excel and it has a text field with numbers, and that text starts with a zero, then Excel will drop the zero.

me@jaykilleen.com wrote this about 1 year ago and it was last updated about 1 year ago.


← Back to the Posts

This is useful for me as we have SAP product hierarchy numbers like '0101' and others like '10'. Excel will convert these to 101 and 10.

This formula converts the number back to text and left pads the text with a '0' if the length is odd.

=IF(MOD(LEN(A2),2)<>0,"0"&A2,A2)&""