{"id":436,"date":"2013-02-17T09:07:00","date_gmt":"2013-02-16T22:07:00","guid":{"rendered":"http:\/\/www.rtb.com.au\/blog\/?p=436"},"modified":"2023-11-14T12:46:12","modified_gmt":"2023-11-14T01:46:12","slug":"dealing-with-leading-zeros-in-excel","status":"publish","type":"post","link":"https:\/\/www.rtb.com.au\/blog\/dealing-with-leading-zeros-in-excel\/","title":{"rendered":"Dealing with Leading Zeros in Excel"},"content":{"rendered":"
\"Format
Format Cells in Excel<\/figcaption><\/figure>\n

I\u2019m a big fan of custom number formats in Excel. People generally think that formatting is all about making your data \u201cpretty\u201d for want of a better term. It\u2019s not. It\u2019s all about making your data easier to read. In this blog I\u2019m going to start by showing you how to deal with leading zeros, aka insignificant zeros, in your Excel spreadsheets.<\/p>\n

\u00a0Entering Leading Zeros into a Cell in Excel<\/h2>\n
\"Enter<\/a>
Enter a number with leading zeros<\/figcaption><\/figure>\n

Let\u2019s start by dealing with leading zeros. For instance, if you need to enter a part number or serial number into a cell in Excel that starts with one or more zeros, excel with ignore them by chopping them off. Excel sees them as insignificant. For example, if you were to type in 000467359, you would be left with 467359.<\/p>\n

Formatting a Cell to Text to Deal with Leading Zeros in Excel<\/h2>\n
\"Format<\/a>
Format a number to text with an apostrophe<\/figcaption><\/figure>\n

You can deal with these types of numbers in 2 ways. Format the cell to text and then type the number into the cell or use a custom number format. If you choose to format the cell to text you can do this as you type the number into the cell by starting the number with an apostrophe. For instance, if we were to use the previous example you would type into the cell \u2018000467359. Excel would then treat the number as text and you would be left with 000467359, left aligned in the cell.<\/p>\n

\"Error<\/a>
Error Indicator in Excel<\/figcaption><\/figure>\n

This is the default alignment for text and a dead giveaway for a number stored as text. You will also be left with a little green triangle in the top left corner of the cell. This is called an error indicator. Click into the cell and click the smart tag that appears, normally to the left of the cell and this will give you the option to \u2013 Ignore error.<\/p>\n

\"Error<\/a>
Error Indicator Smart Tag<\/figcaption><\/figure>\n
\"Smart<\/a>
Smart Tag Options (Ignore Error)<\/figcaption><\/figure>\n

Now that I\u2019ve explained entering a number as text into an excel cell I\u2019m going to tell you that I never use this method. I use a custom number format to deal with numbers like this. All the leading zeros need to be typed in whereas if you use a custom number format, Excel will do the hard work for you. Image if you imported data into Excel with a 100,000 records. Happy typing! Not only that, if you need to change the number of digits or the look and feel of how the number is presented then you have to manually re-enter the data or use formulas to change the data. This is not the case with a custom number format.<\/p>\n

Setting up a Custom Number Format in Excel<\/h2>\n

Let\u2019s now take a look at a custom number format to deal with leading zeros. If you know that the numbers in a column will always be 8 numbers in length and some of them may start with a leading zero then you may want to set a custom number format to deal with it. Firstly, let\u2019s start with a simple example.<\/p>\n