How frustrating is it when you type a telephone number or any number that needs to start with a zero in Excel and the result you get is a number without the leading zero? What can you do? I can tell you that some people can be quite inventive in answering this problem while others just put up with it or work in Word.
Excel takes numbers literally so the leading zero is redundant and therefore removed. If you require it to be there here are some ways you can achieve that.
- Begin by typing an apostrophe. This changes your number into text. This just looks like a number to you but Excel regards it as text. Also it is left aligned.
Type this | ‘0123654789 |
- Type the number you require with the leading zero and place a space somewhere in the number. Again, Excel regards this as text not as a number and it is left aligned.
Type this | 0123 654789 |
- Select the cell or cells you want to fill with leading zero numbers, then start by formatting them as text.
- Right click on the selected area.
- Select format cells from the list.
- Select the first tab named Numbers.
- Select text which is almost at the bottom of the list.
- Select the OK button.
Type this | 0123654789 |
Changing the formatting after typing
A very interesting quirk is that if you type a number first and then format it to be text, it will be seen as text but will behave as a number. If this formatted number is next to a range of number that you will use Autosum to add, this seemingly piece of text will be taken into the range of cells to be summed up.
Resulting in
There is another way too. Using custom formatting.
5. Create a custom format.
- Select the cells you require.
- Right mouse click.
- Select Format Cells…
- In the Number Tab, just like before, select Custom at the end of the list.
- In the box under Type: type in 0##########.
- Select OK.
- Type the numbers you need to in these formatted cells and the leading zero remains. These numbers are right aligned so they are regarded as numbers and will be able to be calculated in a range.
Type this | 0123654789 |
So here we have five different ways of creating numbers in Excel that lead with a zero. Hope this has been of help.
You saved my day! Prefixing ‘ was a cool trick to retain excel data with leading zeros. Kudos to you!