It is not easy to enter a zero at the beginning of a number in a spreadsheet, for a postal code, a telephone number or a reference, for example. Fortunately, there are tricks to force the display of zeros in Excel.

earthpressnews
It is not easy to enter a zero at the

It is not easy to enter a zero at the beginning of a number in a spreadsheet, for a postal code, a telephone number or a reference, for example. Fortunately, there are tricks to force the display of zeros in Excel.

In Excel, the zero is automatically suppressed when used before a number. This can cause problems, for example when you want to enter a phone number. Fortunately, there are several tricks to keep zero as the first character of an Excel cell.

How to enter a number as text in Excel?

If the first character entered is an apostrophe, Excel – like all spreadsheets – considers the entire contents of the cell to be text. But you won’t be able to perform any numerical calculation on it, even if the cell only contains numbers (in addition to the apostrophe)…

  • The moment you enter your number into the Excel cell, to make sure your zero doesn’t disappear, the fastest is to start your typing with a single quote ( ‘ ). For example by entering ‘0689523682 for a phone number, you see that all digits remain displayed, including the first zero.
  • With this trick, the spreadsheet considers the contents of the cell as text and not as a number. You have confirmation from the key Hall pressed: Excel displays an alert in the form ofa small green triangle at the top left of the cell.
27737138
  • To note : to dismiss this alert message, simply click on the yellow diamond and select the option ignore error.

How to apply text format to Excel cells?

The apostrophe trick is very useful when you have one or two numbers to type. When you have a lot of cells to fill in, you can save time by preparing your entry. And the method is just as simple.

  • Select cells to format.
  • Choose the cell format by clicking on the drop-down menu.
  • Select option Text.
27737234

How to keep leading zero and number format in Excel?

In most cases, changing from a “number” format to a “text” format will be enough for you to display a zero as the first character in an Excel cell. But this solution might give you trouble if you want to use the contents of the cell to do calculations. In this case, you need to keep the digital format. The solution is to use custom cell formatting… or a Special format which we will also see.

  • Select the cells to format.
  • Make a right click on the cell range.
  • Choose the option cell format.
27737285

  • In the dialog box that opens, click on the option Personalized of the column Category. In the input box Kindenter the format you want to apply.

  • For example, if you want to enter phone numberstype 0000000000 to force the display of 10 digits, systematically starting with zero. In this case, if you enter 123456789Excel will display a zero before your numberIn the format 0123456789.

27737336

  • Note that this is only a display format. The contents of the cell remain 123456789.

  • For phone numbers, you could also create the custom format 00.00.00.00.00 and enter your numbers in the form 123456789they will look like this: 01.23.45.67.89

  • In the dialog box Format Cells > tab Numberin the left column, also click on the category Special : you will discover special formats to apply to social security numbers, French postal codes, telephone numbers in French, Belgian, Swiss, Canadian formats, etc.

ccn3