This post demonstrates how to add leading zeroes before any value in excel.
Sometimes we have some ID’s or IP’s that have a predefined length and we want them to be shown exactly of equal length, however excel does not show leading zeroes before a numeric value.
To show leading zeroes in such values we will have to add those with the help of excel formula.
Like in the example below, we have data in column A and we want the data to be of length 7 and should be shown with additional leading zeroes to make them of length 7.
We will use a simple formula REPT in excel to add leading zeroes before any value in excel.
REPT repeats the value N times (both the value and N are provided in the argument).
Insert the formula =REPT(“0”,7-LEN(A2))&A2 in cell A2, so this formula is repeating “0” 7 times as specified in the formula as we need the desired value is of length 7.
Drag the formula below to include all the data.
Note-Change the hard coded 7 in the formula to any number to suit your requirement, my goal here was to make the value is of length 7 and hence provided 7.
Hope this helped.
