Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Put 0 In Front Of Number

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

i have a field that has a number in it for example

i want these to always be 4 digits and put zeros in front of the number
how can i do this

View Answers     

Similar Excel Tutorials

Keep Leading Zeros in Numbers in Excel - 2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...
How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as well as adding text to t ...

Helpful Excel Macros

Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data to Show the Top X Number of Items in Excel - AutoFilter
- This Excel macro filters a data set to display only the top X number of items in that data set in Excel. This means tha
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
- This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t

Similar Topics


Can someone please help with adding zeros to the front of a number? Each cell needs to be 12 digits. Any cell with less than 12 digits need zeros in front. Examples below:

CURRENTLY: 4122235443 (10-digits)
NEEDS TO BE: 004122235443 (12-digits)

CURRENTLY: 57655599900 (11-digits)
NEEDS TO BE: 057677799900 (12-digits)

CURRENTLY: 108877443322 (12-digits)
NEEDS TO BE: no changes needed

Thanks in advance!

Hello, i need help for a project that i have.
I have A1 that contain numbers, B1 that contain number, and i want to multiply those 2 and at the end of result i want to add zeros on front. Let me give you an example:

A1 field has data :00.375 B2 has data: 6.49 C1 has the multiply result of A1+B1: 2.43 (But i want when the calculation is done and have the result 2.4 to add 2 zeros on front and become: 002.43 or if the result is for example: 65.20 to add 1 zero on front and become 065.20 , and if the result is 102.20 do not add anything on front.

So in total i need to have the 5 digits of number.

Can someone help please?

I have a field in my Access query that is called RequisitionNbr. The numeric data in this field is not consistent as far as the digits. There are some cells with 1 digit number, 2 digit numbers 3 digit numbers, 4 digit numbers etc. I want to add zeros in front of all cells in that field with < 4 digits. Example if this field has 1 digit number like 3, I want the result to be 0003. If it has 2 digits like 24, I want the result to be 0024. If it has a 3 digit number like 424, I want the result to be 0424. Anything 4 digit and above should just be left alone. How can I put this in a query in access. Thanks for your help


I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.

Thanks in advance


When I do a v-lookup...the worksheet that is displaying the values from the table array is removing zeros from the front of the numbers. Can you tell me how to stop this from happening? The number I am populated is a foriegn key and it is critical the whole number displays all digits.



I'm trying to do this for ages.
Basically, in sheet1, column D, I have a list of names and numbers as follow:

bear - 035888877 MS - 02555887 Coal - 58887441 SE - 02587410

In Sheet2, column Q, I want to extract the number and keep the zeros in front (in case there is a zero). I've tried,
=REPLACE(LEFT(D1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(D1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0 (I found it online).
But I cannot keep the zeros in front! I tried changing cell format to text, didn't help either. I cannot do cutom-0000000 as the lenth of the number can change.

I'm trying to build a macro (recording) doing this but the pb is always the little 0. Please advise if you have an idea?

Many thanks. Fak

Is there a way to keep zeros in front of a number that ALSO shows in the formula bar?

I have product numbers such as 0022 but when I use a vlookup, it doesn't work because it is looking for 22.

I need to keep the numbers in original format for it to work.


An account number should consist of 7 digits. Sheet 1-When the data was downloaded from an application any account that contains a zero in front of the number then it would drop the zero and becomes an 6 digits account (note: not all accounts have a zero in front). How can I normalize this so that when I try to reference/lookup to the correct account number it would know to match.

TIA for your efforts.

Have a s*&%load of ABA numbers. If they are 8 digits they need a zero in front of it.

have issues using the LN formula. Would prefer some decent VBA

basically needs to go down the column and add a zero in front of the number if its 8 digits. Theres about 3600 rows.

EDIT: just a slight edit (s*&%) - We do have kids around - Moderator


We have products that need to be entered into a system using 7 digits. However very few are 7 digits long. When they are not 7 digits '0's need to be put in front of them.

For example 1234 won't work, 0001234 will.

Is there anyway to force a cell to be 7 digits long and replace any missing digits with 0's at the front? To save us tons of time.

Thank you in advance,


I have this script below.
It's on a userform with "td" being a textbox.
The textbox can only have 3 numeric digits in it.
Here's what I'm trying to do.
If the textbox only contains two digits, I want the script to pad it with a zero in front.
If the textbox contains one digit, I want the script to pad it with 2 zeros in front.



Private Sub CommandButton1_Click()
ActiveCell.FormulaR1C1 = td
End Sub

Hi there I have a list of hundreds of account numbers and to make things easier for the person who will upload them into the system they need to be 16 digits long. an account number can be 4-16 digits long for those that are less than 16 we need zeros in front. Is there a way we can all loan #'s in column A have all the digits needed in column b?

I have a lot of record that contain number like shown below:

I want to remove all 0 in front of this number in excel. Can anyone here help me.. I use excel 2007..

I already try using formula


=IF(     LEFT(A1) = "0" ,     RIGHT(A1, LEN(A1)-5),     A1)

but it only remove 5 character in front what about the number that have 6 '0'......
Please help..

Thanks in advance.

i am sure this is probably really easy......i have to copy and paste values from an sap program over to excel spreadsheets, and I usually do about 15 at a time that end up in a column: 15 different cells. The value I am copying are ID numbers that all begin with zero and excel automatically removes the zeros at the front of each number.

Is there a formula/process for preventing this.

Thanks guys!

Hi there

I've got a list of numbers and I have to make them ten digit numbers by adding leading zeros

For instance

12345678 --- 00123456789


784512 --- 0000784512


45 ---- 0000000045

I know I can find the number of zeros by doing a 10-Len(A1) formula. But how do I go about adding that many number of zeros in front.

Best regards

I am making a small macro to convert materials outside of SAP into materials that can be recognized by SAP. Here is a sample material transition I am looking to do.

Convert material 1031560 to 000000000001031560

Obviously, outside of SAP those extra zeros have no value so I remove them. However, sometime I have to add them back in when I want to query specific materials in BW.

The code below is getting me very close to where I want to go. All I need to figure out is how to add in a specific number of zeros to the front of a material number.

In the example above, I would have to add 11 zeros and format the number as text in order to get a legit 18 digit material number (in SAP, all our materials are 18 digits long).

Sample material numbers are in column A and the number of characters in the current material are identified in column b. How can I add in the number of zeros needed as I describe above? Thanks for the help in advance.

Sub SAPMaterial()
Dim x As Integer, rng As Range, c As Range
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A1:A" & LastRow)
For Each c In rng.Cells
'Materials in SAP are 18 characters long, so 18-number of characters in current material number will identify how many zeros I have to add to get a legit material.
c.Offset(0, 1).Formula = "=len(" & c.Address & ")"
'Not sure what to do here to add in the zeros needed to get to a legit 18 digit material
Next c
End Sub

Ok. I am uploading to a database and a Grade field needs to have two chracters ( so leading zeros in front of single digits) My current spreadsheet haso nly 1 character. I thought I had it right but apparently the column was just formatted to display 07 but what is actually in the field is 7. How do I add elading zeros so they are actually data, not just formatting?

I have a list of soc sec numbers that were brought in as numbers so it deleted the first one or two zeros. How can I easily add the one or two zeros back to the beginning of the number?

I am trying to put together a Macro or figure out some simple way which will allow me to change digits out of a string of numbers.

I think creating a macro would be the best bet, but problem is I have no idea where to start. The only type of macro Ive ever used is your basic click and record type, dont know much programming.

say i have a string of numbers

I need to delete the first two numbers (65) add 4 NEW numbers to the front of the string (4420) and then add two zeros to the end of the number.

I want to go from 65987546 to 442098754600

any ideas? There is a list of about 100K line items, and the only way I know how to do it is by writing a formula to multiply and add accordingly which is fine, but Im sure there is a faster way. I also can use the concotante function to add zeros the end of the string, but I dont know what to do about getting rid of the digits at the begining of the string

any help?

The number below is an incident number

200900000036 and is located in column C

in this column I need to have Excel automatically take the digits "200" of the front of this number.

any ideas for me?

I've been struggling with this for a while and desperately need someone's help. I need a macro that will take a 13 digit number, add a zero at the front, change the place of the 9th and 10th digit and then add two more zeros at the end.

For instance:

Take 08347004003486 in Column A and change it to 00834700434008600 in Column B.


Add a zero at the front: 008347004003486.

Move the 9th and 10th digits in Column A to the 12th and 13th spots in Column B: 08347004003486 becomes 008347004340086.

Add two zeros at the end: 00834700430048600.

Finally, move to the next row (A2) and repeat these steps - so on an so forth until the end of the numbers in Column A.

If anyone can help me with this, it would be greatly appreciated!!!

I have a list of data and would like to remove the zeros in front of the numbers. For example...


All of the amounts should be either 3,4, or 5 characters without the zeros. I know that I could sort them and do a right value formula...but I think there may be an easier way by filtering or something.

Any help would be great thanks!

Hey all.

I have two tables that I want to build a query around. However, on one table the 'Account Number' field is 13 digits, prefilled with zeros and the second table doesn't have the prefilled zeros.

For example, account 0012345678910 in my first table is 12345678910 in my second, so they obviously don't 'match' in my queury.

Question: How can I have the Account Number column in my second table prefill with zeros to 13 digits?

Thanks for your assistance.

I receive bulk orders via an Excel spreadsheet, containing many individual
orders with reference numbers.

The reference numbers may be 7-9 digits long, however the field needs to
have 10 digits. The format is set to customize: 0000000000. In this manner,
we always see the 10 digits regardless of the actual number.

The problem we have is that we bulk-load this spreadsheet through a specific
custome built bulk-loader. This reference number field must have 10 actual
digits, not format digits. Therefore a number such as 00865243 becomes
865243 and then will not run through the bulk loader.

A work around is to format the column as text and then insert the leading
zeros manually. This is ok, when the order is between 5 and 20, however we
sometimes get 1500-2000 orders at a time. Manually adding the zeros will not

I have tried converting into comma deliminated, and it still dropped the
leading zeros.

Any suggestions you have will be greatly appreciated.

Ok...hopefully this is simple fix. I wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros. For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros? Any assistance would be greatly appreciated. Thanks!