Hello Excellers, it is time for some more #FormulaFiday Excel fun. If you need to add leading zeros to a number in Excel, the TEXT function is the way to go. This function converts a text string into a number, allowing you to add leading zeros as needed. This tutorial will show you how to use the TEXT function to add leading zeros to numbers in Excel. Let’s get started!
In certYou may need to have data displayed with leading zeros in certain situations. For example, customer ID records, or in my case, I use to store numbers in my databases. So I add leading zeros to maintain data consistency, like 00564 and 03453 as the store ID.
I know I will never use these fields in any calculations, (I am never going to add the actual value of the stI will never use these fields in any calculations (I will never add the actual value of the store numbers together or the exact number values of customer IDs), so maintaining the leading zeros gives a consistent data set. In addition, this proves helpful in using the value in VLOOKUPS or INDEX MATCH to find and fetch the details of my stores in my database.
So, there are few ways to do this but today let’s look at one I do use a lot, and this is the TEXT function.
Add Leading Zeros.
Here is an extract from my data set in the above screen grab. My database has a customer number with five digits. Some customers only have one to four digits, but I want to maintain the five number format. In column C, which will be a helper column, I select C3:C10 and type the following formula.
=TEXT(B4,”00000″)
Then hit CTRL+ENTER to auto-fill the column of selected cells with the formula. All Customer IDs now have five digits, padding out the smaller IDs with zeros until they all have five numbers.
Text Function Syntax Review.
TEXT is a straightforward Excel Function, with two arguments
=TEXT(value format_text)
where
value is a numeric value you want converted into text and
format_text is the formatting you want applied to the value by using the formatting “00000” this forces a zero to be displayed in place of a digit.
Here is the result of the formula being applied.
In order to add leading zeros in Excel, you can use the TEXT function. This function takes a number and converts it into text form. The syntax for this function is =TEXT(number,format). You can use the format codes to change how the number appears. For example, if you want to add leading zeros so that your numbers always have two digits, you would use the code “00”. To try this out yourself, follow the example data set above.
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
