In this article, we will learn Different ways to add zeroes (0s) in front in Excel.
Adding Zero in front of the number in Excel. Default Excel doesn't take zeros in front of the number in the cell. Have you ever tried to enter some data like 000123 into Excel? You’ll probably quickly notice Excel will automatically remove the leading zeros from the input number. This can be really annoying if you want those leading zeros in data and don’t know how to make Excel keep them. Fortunately there are quite a few ways to pad your numbers with zeros at the start. In this article, I’ll explain different ways to add or keep those leading zeros to your numbers.
Different functions to add zero (0) in front
As we know TEXT function is the most used function to add 0 in front but here I’ll explain more ways to add zeros (0s) to numbers.
Format as Text
One of the solutions is converting the format of the number to text because if we input 00xyz, excel considers this value as text and keeps its 0 in front. There are different ways to convert a number cell to text format.
Let's understand these methods one by one. Using a custom format to change the format is the easiest way to change the already existing numbers in range. Just select the range and Use shortcut Ctrl + 1 or select More number format from the drop down list on the Home tab showing General.
Change the format from General to Text.
Now if you try to enter numbers with leading zeros, they won’t disappear because they are entered as text values instead of numbers. Excel users can add a custom formatting to format numbers with leading zeros.
They will only appear to have leading zeros though. The underlying data won’t be changed into text with the added zeros.
Add a custom format to show leading zeros.
The TEXT function will let you apply a custom formatting to any number data already in your spreadsheet.
= TEXT ( Value, Format)
= TEXT ( B3, "000000" )
If you wanted to add zeros to a number in cell B3 so that the total number of digits is 6, then you can use the above formula.
You can force Excel to enter a number as text by using a leading apostrophe.
This means you’ll be able to keep those zeros in front as you’re entering your data.
This method is quick and easy while entering data. Just type a ' character before any numbers. This will tell Excel the data is meant to be text and not a number.
When you press Enter, the leading zeros will stay visible in the worksheet. The ' will not be visible in the worksheet, but is still there and can be seen in the formula bar when the active cell cursor is on the cell.
Another way to get your zeros in front with a formula is using the RIGHT function. You can concatenate a string of zeros to the number and then slice off the extras using the RIGHT function.
The RIGHT function will extract the rightmost N characters from a text value.
= RIGHT ( Text, [Number])
= RIGHT ( "000000" & B3, 6 )
The above formula will concatenate several zeros to the start of a number in cell B3, then it will return the rightmost 6 characters resulting in some leading zeros.
You’ll notice this article describes 9 ways to add leading zeros, but my YouTube video only shows 8 ways.
That’s because I didn’t know you could use the BASE function to add leading zeros until someone mentioned it in the video comments.
The BASE function allows you to convert a number into a text representation with a given base.
The numbers you usually use are base 10, but you could use this function to convert a base 10 number into a base 2 (binary) representation.
= BASE ( Number, Base, [MinLength])
Use the formula
The above formula will convert a number in cell B3 into base 10 (it’s already a base 10 number but this will keep it as base 10) and convert it to a text value with at least 6 characters. If the converted value is less than 6 characters, it will pad the value with zeros to get a minimum of 6 characters.
Definitely a cool new use for a function I otherwise never use.
Add zero (0) only in front of numbers not on text values
Here we will consider the scenario which covers both text and numbers values in the same list. Now excel considers each cell different in terms of format. Now how to let excel know that if the cell has a number value then add 0 in front or else if the cell has text or any other value, then leave it as it is.
For this we will use a combination of two functions IF and ISNUMBER function. Here we have some values to try on.
The Generic formula goes on like
cell_ref : cell reference of the corresponding cell
Use the formula in cell
Explanation: The ISNUMBER function checks the cell for number. IF function checks if the cell has a number then it adds zero in front of the number else it just returns the cell value.
As you can see in the above image the zero (0) is added in front of the number. Let's check if this function works on other values. Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl+D
it will pad the value with zeros to get a minimum of one zero in front of characters. Definitely a cool new use for a function I otherwise never use.
There is another option to add zeros into the pivot table. You can add them into a calculated column in Power Pivot. This way you can use the new column in the Filter, Rows or Column area of a pivot table.
= FORMAT ( Numbers[Number], "000000" )
In the Power Pivot add-in, you can add a new column and use the FORMAT function to create leading zeros in the column with a formula like above. A calculated column calculates a value for each row, so there is no need to wrap the function inside a cell. Now you can use this new column inside a pivot table’s Rows area just like any other column of data.
It can be frustrating to see all your zeros disappear when we don’t know why it’s happening or how to prevent it. As you can see, there are lots of ways to make sure all those zeros stick around in your data. Whatever your requirement, there is surely a good solution for you.
Here are all the observational notes using the formula in Excel
Hope this article about Different ways to add zeroes (0s) in front in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at firstname.lastname@example.org.
Related Articles :
Excel REPLACE vs SUBSTITUTE function: The REPLACE and SUBSTITUTE functions are the most misunderstood functions. To find and replace a given text we use the SUBSTITUTE function. Where REPLACE is used to replace a number of characters in string.
Replace text from end of a string starting from variable position: To replace text from the end of the string, we use the REPLACE function. The REPLACE function use the position of text in the string to replace.
How to Check if a string contains one of many texts in Excel: To check if a string contains any of multiple texts, we use this formula. We use the SUM function to sum up all the matches and then perform a logic to check if the string contains any of the multiple strings.
Count Cells that contain specific text: A simple COUNTIF function will do the magic. To count the number of multiple cells that contain a given string we use the wildcard operator with the COUNTIF function.
Popular Articles :
50 Excel Shortcuts to Increase Your Productivity : Get faster at your tasks in Excel. These shortcuts will help you increase your work efficiency in Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.