Add Leading Zeros in Excel
- Add Leading Zeros for Numeric Values
- Add Leading Zeros for Non-numeric Values
- Which One is the Best Solution?
Remove Leading Zeros in Excel
Add Leading Zeros in Excel
Add Leading Zeros for Numeric Values
How to add leading zeros to numbers in column A?
Solution #1, using the Format Cells menu
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.
Solution #2, using TEXT function
The Formula
=TEXT(A1,"0000000")
The Result
The result is the same as solution #1, but all data in column B are text, not numbers.
The formula bar displays the excel formula, not the data, to display the data, do a Copy – Paste Special – Values for all data in column B.
There are leading zeros in front of the number
Add Leading Zeros for Non-numeric Values
Solution #3, using REPT and LEN function
How to add leading zeros to text?
The Formula
=REPT("0",7-LEN(A1))&A1
The Result
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.
Which One is the Best Solution?
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
Remove Leading Zeros from Numeric Values
Solution #4, using Format Cells menu
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.
Solution #5, using a mathematical formula
You can use all mathematical formulas to remove leading zeros, in one condition do not change the original value.
Addition formula
=A2+0
Subtraction formula
=A2-0
Multiplication formula
=A2*1
Division formula
=A2/1
All formulas above return the same results and all text changes to numbers.
Remove Leading Zeros from Alphanumeric Values
Solution #6, using Convert menu
All data changes to numbers.
Solution #7, using VALUE function
The Formula
=VALUE(A1)
The Result
All leading zero disappears, and all data become numbers.
Solution #8, using TEXT function
The Formula
=TEXT(A1,"#")
The Result
All leading zero disappears, but the data remains as text.
Remove Leading Zeros from Non-numeric Values
Solution #9, using Excel Formula
The Formula
=RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1,"0",""),1),A1)+1)
The Result
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)
=SUBSTITUTE(A1,"0","")
All “0” (zero) characters disappear. Next is the LEFT function task to find the first character after leading zero.
The Formula (Step2)
=LEFT(SUBSTITUTE(A1,"0",""),1)
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)
=FIND(LEFT(SUBSTITUTE(A1,"0",""),1),A1)
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)
=RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1,"0",""),1),A1)+1)
The Formula (Step4, another alternative)
=MID(A1,FIND(LEFT(SUBSTITUTE(A1,"0",""),1),A1),LEN(A1))
Which One is the Best Solution?
To remove leading zero from a number, do the opposite step of adding leading zero. Use the Format Cells menu (solution #4).
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).
