A common issue is Excel losing leading zeros when certain items are typed in or imported into a spreadsheet. This could be cell phone numbers, account numbers or other numbers. So for example the account number 000 000 100 would appear as 100 in Excel but you need it to show all the characters.
Table of contents
YouTube for Excel losing leading zeros (and big numbers)
Reason Excel loses leading zeros
The reason for this is that Excel makes a quick guess of the type of data you are giving it and changes that cell to be that data type. If all the characters in a cell are numbers, Excel will guess that it should be a number and as no number has zero’s in front of it, it removes them and leaves the number behind.
If you want to force it to show all the leading zeros you can try one of the following:
Use an apostrophe ( ‘ )
If you are typing the number in, you can start with an apostrophe ( ‘ ). You will see that the leading zeros will be retained and you will not see the apostrophe in the cell. This tells Excel that the ‘numbers’ are actually text and it must keep everything you type. So for example if I type ‘083 123 4567 it will show 083 123 4567. If I do not include the ‘, it will show 831 234 567.
Tell Excel it is Text part 1- Typing it in
If you do not want to type the ‘ every time, then you can format the relevant cells as text. As shown below, highlight the cells and format them as text. Now when you type, Excel will treat everything as text even if it is a number with leading zeros.
Tell Excel it is Text part 2- Importing data
If you import data from another file e.g. a csv, Excel may automatically remove any leading zeros. You can stop Excel doing this in the import wizard. So in Step 3 of 3 as shown below, you can click on the relevant column (2nd red arrow) and then tell Excel it is text (1st red arrow). Now Excel knows it must import the data as is and not ‘make’ it a number by dropping the leading zeros.
