Add Leading Zeros in Excel
How to add leading zeros to numbers in column A?
Make a range selection A1:A7. Press CTRL+1 (Format Cells shortcut). Select Custom on the Number tab. Type a 7-digit zero number in the Fill type section, then click the OK button.
All numbers with less than seven digits have a leading zero. Pay attention to the red arrow at formula bar; there is no leading zeros, only number 1. Its means leading zero is only the appearance, the data is still a number.
The result is the same as solution #1, but all data in column B are text, not numbers.
How to add leading zeros to text?
The excel REPT function write a text in N times. To get the N value, use the LEN function and subtract 7 by the LEN function result. The result is leading zero for non-numeric values.
To add a leading zero and keep the original values as a number, Format cells is the only option.
To add leading zero and convert it to text, you have two option. The excel TEXT function produces a simpler formula but adding leading zero for numeric values only. The otherwise, The REPT and LEN function able to add leading zero for numeric or non-numeric values.
The choice is yours.
Remove Leading Zeros in Excel
Solution #4 is the opposite step in solution #1. Change cell format from custom to general.
Make a range selection A1:A7. Press CTRL+1 (Format Cells shortcut). Select General on the Number tab, then press ENTER.
No leading zeros for all numbers.
You can use all mathematical formulas to remove leading zeros, in one condition do not change the original value.
All formulas above return the same results and all text changes to numbers.
All data changes to numbers.
All leading zero disappears, and all data become numbers.
All leading zero disappears, but the data remains as text.
How does the formula above work?
The formula above looking for the first character position after leading zero, then take the character afterward.
The trick to finding the first character after the leading zero is to eliminate all “0” (zero) characters using the SUBSTITUTE function.
The Formula (Step1)
All “0” (zero) characters disappear. Next is the LEFT function task to find the first character after leading zero.
The Formula (Step2)
After getting the first character after leading zero, the next step is to find the position of the character in the original data using the FIND function.
The Formula (Step3)
After obtaining the first character position, the next step is to take all the characters from the first character position to the last.
Which function to use? Can you use the RIGHT function or MID function? Using the RIGHT function or MID function both need the LEN function.
The Formula (Step4)
The Formula (Step4, another alternative)
There are two options to remove leading zero from alphanumeric data, convert it to numbers or keep it as a text. To convert to a number the easiest way is to use the Convert menu (solution #6). If you want to keep it as a text, use the TEXT function (solution #8).
The hardest way is to eliminate leading zero for non-numeric value. You must use an excel formula and involve many functions, but that is the only option you have (solution #9).