How to Add Leading Zeros in Excel (2023)

Excel contains many useful and valuable functions for the user. Still, it has many quirks and issues that might be annoying to someone who isn't well-versed in Excel.For example, zeros in Excel can be an unwieldy number. Microsoft Excel automatically removes leading zeros or leading zeros from a cell when you enter zeros in a specific numbering system. This may be the case if you keep records in Excel, such as serial numbers, social security numbers, format codes, zip codes, account numbers, employee IDs, zip codes, or phone numbers that start with zeros. In this article, you'll learn how to control leading zeros in spreadsheets and switch between modes based on your current work. You also canfollow us on youtubefor more modeling tips, tricks and tutorials.

How to add leading zeros in Excel?

Considering that you may not like adding leading zeros in Excel manually, there are many ways to add leading zeros in Excel using various Excel functions automatically. We will learn all these other methods in detail in the following lines: Enter some leading zeros in the cells as shown in the image below. Now press Enter and you will find that the numbers disappeared because Excel does not read them automatically.the zeros to the left.Now the leading zeros are gone and I guess your workday has just gotten more challenging. There are different ways to add leading zeros in Excel. Let's discover the methods that suit your needs.

Method 1: How to add leading zeros using the Format Cells dialog

To apply this custom formatting method and add leading zeros, follow the steps below: To make your job tasks of adding leading zeros easier, it can be good to know the number of digits in each cell through a simple formula.Treat the:Type these LEN functions into an empty cell to find out the number of digits: =LEN(A1) Later in this method, you will learn the importance of the above formula for adding leading zeros in Excel. Note: The formula is typed in the cell and appears in the Excel text function string above or in the formula bar. See the red box on the screen.After typing the above formula in the cell, press Enter, and the number of digits entered in the number procedure will be displayed in the cell. As below, see screenshot.Note: You should change the column number and letter each time you type the other formulas depending on where in the cell you want to know how many digits it has. For example, the numeric formula will be =LEN(A1) if you want to know the number of digits in the cell of column A, row 1. And it will be like the following formula =LEN(B2) if you want to know the number of digits in the cell located in column B, row 2. etc. Type the same formula in each cell; you need to know the number of digits in the text string.Trin a:Select all the cells you want to edit and add leading zeros. You can do this by dragging the cursor or by holding down the transform file on the keyboard while clicking the mouse. As below, see screenshots. You can select adjacent cells by clicking keyboard shortcuts as follows: click the first cell in the cell field with the left mouse button, then press the Shift button on the keyboard, finally click the last one cell you want to access in the cell field while holding down the Shift key. Step three:Right-click on your selection and select Format Cells. Alternatively, you can use the keyboard shortcut of pressing Ctrl+1 to bring up the cell format window faster. trill fire:The Format Cells dialog box appears. Select Custom Number Format or the Numbers tab. As shown in the screenshot. garbage phase:Select Custom format cells. step six:Create the following custom number format by adding as many zeros as you get the number of digits. step seven:You can add zeros to your custom formatting code by replacing the general type box word or text string with zeros.Note: The format code or the number of zeros here are variable length numbers. It is chosen according to the number obtained by the formula. You can add five zeros or six or more. In our example, we add fixed length numbers or a fixed number of zeros, 10 zeros to add only one leading zero, or insert fixed zeros before a number.step eight:Press Ok to add the leading zeros.This is the end result of adding leading zeros in the custom format. Note here that this method did not change the format of the number; displays only the fixed length of previously added zeros without altering the cell format. See image below.There is a problem with this method: it only works later. You must set the cell format for text values ​​before you type any text. If you try to do it after that, the zeros will be gone and you'll have to rewrite them. Note: Selecting a formatted cell will still display the original number in the formula bar. They only appear in the spreadsheet with leading zero formats. What changes is simply the way it is displayed. This also means that if you copy and paste the data as values, you will lose the leading zeros. It might help if you lookHow to change currency in Excel

Method 2: How to add leading zeros by adding an apostrophe

The LEN function method is not the only way to preserve leading zeros. But adding a leading apostrophe A quick and easy way to achieve the same result is to use an apostrophe, a single quote, or a magic apostrophe. This prevents Excel from automatically converting your number. You can keep leading zeros in the data set available in Excel by using this method in quotes ('). For example, if you have phone numbers or numeric values, keep your arithmetic calculations correct. To do so, follow these steps to add leading zeros:Treat the:Type this apostrophe (') at the beginning of the number or numeric value. As below, see screenshot. Trin a:Press Enter and it will keep the leading zeros in this cell. See the screenshot.As the example above shows, this method will not change the format of the number or customs number. It only shows leading zeros without altering the cell format. Note: You may see a small green triangle (an error indicator) in the upper left corner of the cell and when you select this cell.This will display a warning sign indicating that you have saved the number as text. The opening apostrophe appears only in the formula bar, not in the cell.Also note: This method may achieve the desire to keep leading zeros, but it is not practical or productive. It can take a long time to do this on each individual cell. It may be a suitable method for you when you are importing facts or figures for the first time; in this case the offer method will not take much time and may be the easiest to follow.

Method 3: How to add leading zeros using a custom format as a text value

This method is very productive and somewhat similar to the first method in just a few steps. So how do you convert numbers to text in Excel without losing zeros?Treat the:Select all the blank cells you want to insert and add leading zeros. You can do this by dragging the cursor or by holding down the transform file on the keyboard while clicking the mouse. As below, see screenshots.

You can select adjacent cells by clicking the keyboard shortcuts as follows:

Click the first cell in the cell field with the left mouse button, then press the Shift button on the keyboard, finally click the last cell you want to access in the cell field while holding down the Shift button.Trin a:Right-click on your selection and select Format Cells. Alternatively, you can use the keyboard shortcut of pressing Ctrl+1 to bring up the Format Cells dialog faster. Step three:The Format Cells dialog box appears. Select Custom Number Format or the Numbers tab. As shown in the screenshot. trill fire:Select the format of the text. As shown below in the screenshot. garbage phase:Press OK to get the text values ​​in the selected cells. See the screenshot.This method will cause Excel to treat the number format as a text string, not a number. Entering a number with leading zeros will force Excel to display the number as if it were not a number.step six:Enter a number ending in zero and press Enter.See the image below; Leading zeros appear in the cell format.Note: You may see a small green triangle (an error indicator) in the upper left corner of the cell and when you select this cell.This will display a warning sign indicating that you have saved the number as text.

Method 4: How to add leading zeros using the text function

The text function is one of the best Excel tools for keeping leading zeros. This is roughly similar to setting a custom number format in the Format Cells dialog. So, let's learn what the text function does in the following lines: The text function allows you to reference text or numbers and then choose the output format. This function uses the syntax "=text(value, format)". The value can be any number or cell reference. The format follows the same structure as the custom number option, except that the format is enclosed in quotes. The value function converts the value in A1 to a number. As an example, the following formula converts the number in cell A1 to an 11-digit phone number: =text(A1, "0000-000-0000") This method can copy and paste numeric data into other cells while adding zeros to the cells. left to the new cell. Please note: You will have two copies of the data; The original version that does not include the leading zeros and the new version that contains the actual number plus the leading zeros. So how can you use the text function to display leading zeros? Follow these steps to keep leading zeros in text format using this TEXT formula:Treat the:Enter this formula TEXT = text(A1, "0000-000-0000") into any empty cell. As shown in the screenshot.Note: You will find the text formula in the cell and in the formula bar. As shown in the text string inside the red box. See image below. Trin a:Press Enter. Find the exact number in the original cell plus leading zeros. See image below.For more great Excel tips and tricks, check out our blog post.excellent fun

Method 5: Insert the same number of leading zeros into numbers using the concatenation function

The custom text and number formats feature specifies a specific number of digits. However, the number of leading zeros varies depending on the number of digits entered, excluding leading zeros. However, the concatenation function allows you to add a specific number of leading zeroes, regardless of how many digits are included in the source cell. Use the structure "=Concatenate([nulls], A1)" and enclose nulls in quotes. To add three zeros to text or numbers in A1, e.g. use the formula =CONCATENATE("000", A1) Add or insert the same number of leading zeros into numbers using the Concatenate function. If you want to insert a certain number of leading zeros into each number, e.g. three leading zeros for each number, try the concatenation function.Treat the:Enter the formula =CONCATENATE("000", A1) into an empty cell. Trin a:Press Enter.Three zeros have been added before the numbers based on the leading zeros. See the screenshot above. The formula bar shows the added formula while the numbers are displayed in the cell.In this article, we provide several ways to achieve the same result, and as the methods above show, there are plenty of options available if you're looking for a simple way to add leading zeros to cells in Excel.If these methods are too difficult to use, require more work, or you can't afford to use them because you're short on time, you can learn how to use Excel functions to process your data on a large scale using the ready-made template. for use provided by Simple Sheet. It is estimated that Excel is used bymere end1.2 milespeople around the worldin more than 140 countries. This widespread use brings with it the need to accommodate and process data specific to different countries. Therefore, you can view the catalog of ready-to-use and simple spreadsheet templates here.

Do you want Excel to work for you? Try 5 amazing Excel templates and 5 unique lessons

We hate SPAM. We will never sell your information for any reason.