Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Miscellaneous Pad a number with zeros

Pad a number with zeros

While working with Excel, we are able to pad a number with zeros by using the TEXT and REPT functions, or by simply changing the format of the number.  This step by step tutorial will assist all levels of Excel users in padding a number with zeros in three different methods.  

Figure 1. Final result:  Pad a number with zeros

Method 1 formula: =TEXT(B4,"0000000")

Method 2 formula: =TEXT(B4,REPT("0",7))

Method 3: Using the Custom format “0000000”

Syntax of the TEXT Function

TEXT converts a number into text

=TEXT(value, format_text)

  • value – any numeric value, a formula or cell reference to a numeric value
  • format_text –  a numeric format expressed as a text string and enclosed in quotation marks

Syntax of the REPT Function

REPT repeats text for a specified number of times

=REPT(text, number_times)

  • text – the text we want to repeat
  • number_times –  the number of times to repeat the text; must be a positive number

Setting up our Data

Our data is the number “2468” in cell B4.  We want to pad this number with zeros such that the resulting value has seven digits.  We will record the padded number in cell D4.

In this example, we will learn to pad a number with zeros by using three different methods:

  • Using TEXT function
  • Using TEXT and REPT functions
  • Customizing the format

Figure 2. Sample data to pad a number with zeros

Pad a number with zeros using TEXT function

Padding a number with zeros using the TEXT function can be done in three easy steps:

Step 1.  Select cell D4

Step 2.  Enter the formula: =TEXT(B4,"0000000")

Step 3.  Press ENTER

Figure 3. Entering the formula using TEXT function

Our formula converts the number in cell B4 into a text with the format “0000000”, which means 7 digits.  In this case, the number we want to convert has fewer digits than the zeros in the format.  Hence, TEXT displays insignificant zeros to be able to return seven digits as per our given format.  

As a result, the padded number in cell D4 is “0002468”.

Pad a number with zeros using TEXT and REPT function

There is an alternative solution to padding a number with zeros by combining the TEXT and REPT functions in one formula.  Let us follow these steps:

Step 1.  Select cell D4  

Step 2.  Enter the formula: =TEXT(B4,REPT("0",7))  

Step 3.  Press ENTER  

Figure 4. Entering the formula using TEXT and REPT functions

Our formula converts the number in cell B4 into a text with the given format.  Our format s determined by the REPT function: REPT(“0”,7).  The REPT formula is another way to represent the format we want.  In this case, REPT returns a text string with the digit zero “0” repeated seven times “0000000”, which is the same as in the previous example.  

As a result, the padded number in cell D4 is “0002468”.

The REPT function is especially useful when the number of digits we want for our padded number is variable.  We can link the argument number_times  with a cell reference or a formula.  

Pad a number with zeros by custom format

The first two methods we have learned both convert the number into a text value.  When we want our padded number to remain as a numeric value, we can simply customize the format by following these steps:  

Step 1.  Select cell D4  

Step 2.  Enter our value “2468”.  

Step 3.  Press Ctrl + 1 to launch the Format Cells dialog box.  

Step 4.  In the Format Cells preview pane, select Custom.

Step 5.  In the Type box, enter the format “0000000

Step 6.  Press Enter

Figure 5. Customizing the format to pad a number with zeros

The resulting number still has the value “2468” but it is displayed as “0002468”, which is the same as in the previous methods.

Figure 6. Output: Pad a number with zeros by using custom format

The table below shows the three methods having the same final result.  

Figure 7. Three different methods to pad a number with zeros

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
Good morning, I need a cell formatting solution to display leading zero in my excel table with the following conditions. 1. Cannot format as text 2. cell size is not fixed. Please let me know if there is a solution.Thanks in advance
Solved by V. C. in 11 mins
i want to lookup a alphanumeric value(eg. EMP789 ) from a coloumn range i use =VLOOKUP(REPT("z",255),C:C,1) to get text value
Solved by K. H. in 25 mins
looking for an Iferror formula that will bring back a blank value if a date is not listed. Currently using the following but want to add an Iferror statement: =TEXT(H2,"DDDD"). H2 is the raw date ex. 2/8/18. I tried =IFERROR(TEXT(H2,"DDDD"),"") but it keeps returning "saturday" instead of a blank cell. Any help is appreciated.
Solved by F. Q. in 20 mins
How do I make this formula =Value(IFerror(TEXT(AM3,"HMM"),"0")) Return a value of 2400 if the value is not an error?
Solved by F. J. in 21 mins
I have an excel spreadsheet with two worksheets. And the following formula is not pulling the data. =VLOOKUP($A$2:$A$566,'Module Type Info'!$A$2:$D$97,4,FALSE) In sheet 1, I am using all data in column 1 for the lookup In sheet 2, I have selected the first 4 columns of data for array I want to pull data from sheet 2, column 4 into sheet 1 Both tabs are sorted alphabetically. Confirmed that the value in column 4 of sheet 2 is a TEXT field. Why is this not pulling the info from sheet 2 into sheet 1
Solved by F. L. in 39 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc