How to Keep Leading Zeros in Excel

Excel is a great tool for data manipulation and transformation but is not robust at keeping leading zeros. Learn how to keep leading zeros in excel through these tips.

The reason why leading zeros disappear in excel is due to its method of identifying data types. Excel assumes the data types of the data that you input into it. Whenever you put in an entry with leading zeros, it think its an integer and therefore trims out the 0 in the beginning.

That is a problem!

Here is how you can solve for it:

1. Use the TEXT() formula. This formula converts an integer into text. Here is an example of how you can use this. Say I want to keep my leading zero when entering this Account ID: 0292934. In excel, this will default to 292934 You can use this formula: =TEXT(“292934”, “0000000”). This will work when you know exactly how many digits the account id will have.
2. Another way is to use the Text to Columns. This feature in excel allows you to select how to define the data type of the column. Instead of General, put Text and the leading zeros in excel will stay.