Hello time for some more #FormulaFun. Today we are looking at using the TEXT function to add leading zeros to numbers.
In certain situations you may need to have data displayed with leading zeros. For example -customer ID records, or in my case I use store numbers in my databases and I add leading zeros to maintain data consistency for example 00564 and 03453 as the store ID’s.
I know I will never use these fields in any calculations, (I am never going to add the actual value of the store numbers together, or the actual number values of customer ID’s), so maintaining the leading zeros gives a consistent data set. This proves very useful for 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.
Here is an extract from my data set. My database has a customer number with 5 digits. Some customers only have from 1 to 4 digits at present, but I want to maintain the 5 number format.
In column C which will be a helper column, so I select C3:C10, and type the following formula
Then hit CTRL+ENTER to auto fill the column of selected cells with the formula. This displays all of the Customer ID’s with 5 digits, padding out the smaller ID’s with zeros, until they all have 5 digits.
TEXT is a straightforward Excel Function, with 2x arguments
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.
If you want to download the Formula Friday example workbook then you can do so here.
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.