How to add 0 in front of number in excel

Using TEXT function

To add a zero in front of a number in Excel, you can use the TEXT function.

Here are the steps:

  1. Select the cell where you want to display the number with a leading zero.
  2. Type the formula “=TEXT(A1,”00000″)” (without the quotes). Replace “A1” with the reference to the cell that contains the number that you want to format.
  3. Press Enter to apply the formula.

Now the cell will display the number with a leading zero.

For example, if cell A1 contains the number 123, then the formula “=TEXT(A1,”00000″)” will display “00123”.

You can modify the number of zeros in the format string to match the desired length of the result.

For example, if you want to display a five-digit number, use “00000” as the format string.

Using custom number format

To add a zero in front of a number in Excel, you can use a custom number format. Here are the steps:

  1. Select the cell or cells that you want to format.
  2. Right-click on the selection and choose “Format Cells” from the context menu.
  3. In the Format Cells dialog box, click on the “Number” tab.
  4. Select “Custom” from the Category list.
  5. In the “Type” field, enter the desired number of zeros followed by the desired format code. For example, if you want to display a five-digit number with leading zeros, enter “00000” (which indicates that there should be five digits, with leading zeros added as needed).
  6. Click “OK” to apply the format.

Now any number entered into the selected cells will have leading zeros added to it. For example, if you enter “12” into a cell formatted with “00000”, it will display as “00012”.

Simply format the cell as “Text”

To add a zero in front of a number in Excel, you can simply format the cell as “Text” and then enter the number with a leading zero. Here are the steps:

  1. Select the cell or cells where you want to add the leading zero.
  2. Right-click on the selection and choose “Format Cells” from the context menu.
  3. In the Format Cells dialog box, click on the “Number” tab.
  4. Select “Text” from the Category list.
  5. Click “OK” to apply the format.

Now you can enter any number into the selected cells with a leading zero, and it will be displayed exactly as you typed it. For example, if you enter “0123”, it will display as “0123” instead of just “123”.

Leave a Reply

Your email address will not be published. Required fields are marked *