< Go Back

Pad a number with zeros

PADDING NUMBERS WITH ZEROS

If you want to PAD a number with ZEROS, you can incorporate the TEXT function into a BASIC FORMULA and also use the REPT function Numbers can be PADDED with zeros for DISPLAY ONLY and PADDED such that the number changes into a TEXT.

PADDING A NUMBER FOR DISPLAY ONLY

Generic formula based on the example below: Click on number, format cells, custom, “00000”

To PAD a NUMBER for DISPLAY ONLY (the number remains a true number), follow the steps below.

Example

Step 1

  • Input the data as shown below

 

Step 2

  • Click on CELL C6

  • Once it is clicked, the fx bar should contain the number (444) as shown in figure 2

Step 3

  • Click on Format

 

 

  • When you’ve clicked it, click on format cells
  • When you click on format cells, you will have the figure below.

  • Click on Custom as shown in the figure below
  • On the type bar, let’s type in five zeros as shown because we want this cell to have five digits as shown by Cell B6

  • Click on OK and you will have the figure below

Based on what we have done, the number remains a true number.

PADDING A NUMBER TO CHANGE INTO TEXT

BASIC FORMULA

=TEXT(C7, “0000000”)

  • Highlight Cell D7. Then type the basic formula string into the fx bar. When you click on OK, you should have the result in figure 7.

It is pertinent to remember that PADDING in this way will CHANGE THE NUMBER INTO TEXT. If you want the number to remain numeric, use the previous method on PADDING A NUMBER FOR DISPLAY ONLY.

PADDING A NUMBER WITH THE REPT FUNCTION

BASIC FORMULA

=TEXT(C8,REPT("0",8))

The REPT function is used to type a numeric character that causes the number, in this case, zero, to be repeated based on the numeric character. In other words, it is a function that repeats a number (0) for a specified numeric value. In this example, ZERO is repeated 8 TIMES.

Highlight Cell D8. Type the basic formula into the cell. The interpretation of this formula is that zeros should be placed before the first positive integer in Cell C8 such that the total number of digits remains 8. This is shown in the figures below.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar