1. Convert a Number into a Text for Adding Zeros
Here is a thing if you change the format of a cell from number to text then you can add a leading zero without any problem. This way Excel will not treat your number as an actual number and will not delete any zero from it.
- Select the cells in which you want to add the leading zeros.
- Go to Home ➜ Number ➜ Change the number format to text.
Now you can easily add zeros at the start of the numbers. You can’t use these numbers further in any type of calculation because they are now in text format.
2. Use Concatenate Function to Add a Zero
If you want to insert fixed numbers of zeros before a number, you can use concatenate function. With CONCATENATE function, you are combining zeros with actual numbers. You can use a formula like the one below to add a zero before numbers in a cell.
You can’t use these numbers further in any type of calculation because they are now in text format.
3. Add Dynamic Leading Zeros with REPT Function
REPT is one of my favorites Excel functions which can repeat a text a number of times.
In the above example, we have used REPT to get a total of 8 digits in a cell.
This is a dynamic formula to get zero before the numbers. You can’t use these numbers further in any type of calculation because they are now in text format.
5. Use TEXT Function to Add Leading Zeros Format
You can also use the TEXT function to add leading zeros.
=TEXT(B6,"0000000")
In the above example, we have converted a number into a text in which you will always get 7 digits.
In the TEXT function, you just have to refer to the cell which has the value and then the format as I have specified in the above example. You can’t use these numbers in any type of calculation because they are now in the text format.
5. Apply Custom Format with Leading Zeros
If you don’t want to use any formula and all other methods, you can apply a custom format to a cell. I believe this is the best method to use and it has an additional benefit over all the other methods.
- Select the cell in which you want to add zeros as a prefix.
- Right-click ➜ Format Option or you can also use the shortcut key Ctrl + 1 open “Format Cell Dialog Box”.
- In Category ➜ Select Custom.
- In the custom input dialog box, enter “0000000”.
- Click OK.
In the above example, we have formatted the cells with 7-digit numbers in which you will always get 7 digits. If you have 5 digits number rest of the 2 will be zeros at the start.
If you check Excel’s formula bar, you have the actual number value in the cell and you can use this number for calculation which is not possible in the rest of the methods.
6. Create a Custom Function to Add Zeros Before a Number
Let’s say you need to add zeros more frequently while working with data. In this case, you can write a code for a custom function. With this function, you need to refer to the cell where you have the value, and then, in the second argument, you need to define the number of zeros you want to add.
Function AddLeadingZeros(inputValue As Variant, numZeros As Integer) As String
' Check if input is numeric
If IsNumeric(inputValue) Then
' Convert the input value to a string
Dim inputStr As String
inputStr = CStr(inputValue)
' Add the specified number of leading zeros
AddLeadingZeros = String(numZeros, "0") & inputStr
Else
' If input is not numeric, return an error message
AddLeadingZeros = "Invalid Input"
End If
End Function To add this code in your workbook: Press Alt + F11 to open the VBA editor, and insert a new module by clicking Insert > Module. Paste the code into the code window.
=AddLeadingZeros(A1, 3)
A1 is the cell with the value you want to add leading zeros to, and 3 is the number of leading zeros to add.
7. Use TEXJOIN Function to Add Zeros Before
You can use TEXTJOIN with a combination of REPT and LEN to get a number with leading zeros. It combines multiple text strings into one, with an option to include a separator between each string. You can also choose to ignore empty cells when joining the text.
=TEXTJOIN("", TRUE, REPT("0", 5 - LEN(A1)), A1) Let’s break down the formula into parts to understand it:
LEN(A1)finds the length of the value in cell A1. For example, if A1 contains “123”, LEN(A1) will return 3.A15 - LEN(A1)calculates how many leading zeros are needed. It subtracts the length of A1 from 5. So, if A1 has “123” (length 3), 5 – 3 equals 2.REPT("0", 5 - LEN(A1))creates a string of zeros. It repeats the zero character as many times as the result of 5 – LEN(A1). In our example, it repeats “0” two times, resulting in “00”.TEXTJOIN("", TRUE, …)combines all values together. The TEXTJOIN function joins text strings. The first argument “” means it joins them without any spaces or characters in between. The second argument, TRUE, tells it to ignore empty cells, but it doesn’t apply here since we’re not dealing with multiple cells.
Finally, the formula combines the string of zeros from Step 3 with the original value in A1. If A1 is “123”, it becomes “00123”.
8. Use Flash Fill to Add Zeros
You can also use flash fill to add zeros before a number. It automatically fills in values based on your patterns. But first, make sure the result column is formatted as text.
- Type the desired format with Zeros: In the cell next to your first number, type the number with leading zeros (e.g., if the number is 1145, type 00001145).
- Enter to the Next Cell: After that, you need to press the enter key to come to the cell downwards.
- Press the Flash Fill Keyboard Shortcut: In the second cell, use the keyboard shortcut Control + E. And the moment you do this, it will fill all the other values with the same number of zeros before.
Another Formula with MID
You can also use the below formula which combines MID and LEN to get a number from the cell and add 5 zeros before it.
=MID("00000" & A1, 1, LEN(A1)+5 First, it combines “00000” with the value in cell A1. Then, it extracts a string starting from the first character and ending with a length equal to the original number’s length plus 5, ensuring the total length includes the leading zeros.
Conclusion
Most of the time you need to insert a zero before a number when we are dealing with some specific type of numbers i.e. invoice numbers, mobile numbers, zip codes, etc.
With normal numbers, this kind of situation never arises because we are more into calculation not about the format.
But if it arises, you have now 5 different methods to deal with it and I hope these methods will help you but now tell me one thing.
Do you have any other method to add leading zeros before a number?
Make sure to share your views with me in the comment section, I’d love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.
Insightful.
after custom formatting my numbers start at 000002, when I select “fill in series” the next number is 000012… how do I get it to increment by 1 number only… should be 000003?
Thank you so much. It is amazing.
This was not helpful
Excellent!!
Very much informative. thanks a lot.
Hi Puneet,
Excellent work. Keep it up.
i found the 3rd option (REPT function) the best. you can customise the length of the number by linking “7” in the example shown to a cell with value of desired length.
All the Best.
Very useful tips, I can add “0“ to my number in Excel for the first time thank you.
Hi,
I have this formula for date value =A1-Date(Year(A1),1,0) everything works fine. range A1 contains 01/01/2019. but when I modified the code =”LTL”&A1-(Date(Year(A1),1,0)&”/”&Text(A1,”yy”) for my wish “LTL001/19”. the return value has no leading zeros like “LTL1/19”.
the text “LTL” and “/” are static. 001 change if days changed, 19 also change if years changed
what should I do for fix this?
thanks in advance.
how can we get the same no. in fx coloum after adding the 0
You can also use the ‘ before a number e.g ‘01233
Hi Puneet
My variation on your number 3 is
=right(REPT(“0”,99)&A1,FieldLength)
where Fieldlength is a named value (or you can refer to a cell if you prefer).
The reason I prefer it is that you only have to point to one cell when creating it,
and can amend the length from a single place even if the formula is scattered all over your workbook.
Keep up the good ideas, I really appreciate your approach.
Best wishes
Adrian
When you load a file where an apostrophe has been used, or the column has been formatted as text, Excel will still convert it to a number and drop off the leading zero(s). I don’t have control over the files I receive.
hello to everyone from Italy.
‘02345
i use the apostrophe in front of the number
My issue is loading csv files. The number can be formatted and saved with leading zeros, and even have inverted commas, but Excel converts them back to ordinary numbers when you reload the file.
Format the columns where you want to enter csv numbers as text. Highlight the columns, then Ctr+1, then select text and click ok.
Thanks Baah.
However, mine is an automated process where the invoice/account numbers are of differing lengths and may have differing numbers of leading zeros which I have now lost irretrievably.
Hi puneet, you are awesome. You have really made excel as very enjoyable.
For leading zero – I always use ‘ (apostrophe) before typing in any number. The cell retains whatever you type.
I’m so glad you liked it.
Method 5 is the best
Yup.
Do you have a way to autonumber in a table? I want to automatically add a unique Order ID every time a new order is entered in a table.
Hey Lisa,
How you are entering data in your table. I mean manually or by some kind of userform.
Manually.
Please Check This File.
I hope this will help
http://goo.gl/aBd985
Excellent, thank you! I find your blog very useful!
Hey Lisa,
Thanks for your words.
Hey Lisa,
Thanks for your words.