This post will guide you on how to count numbers with leading zeros in Excel 2013/2016/2019/365. When working with numbers that have leading zeros, it can be challenging to count the number of zeros that appear before the actual value. Fortunately, there are two main methods for counting leading zeros in Excel: using a formula or using VBA code.
In this post, we’ll walk through the steps for each method, so you can choose the one that works best for your needs.
1. Count Numbers with Leading Zeros using Formulas
Suppost you have a list of product ID in your worksheet, and the product ID contains the different number of leading zeros in the front of string. And if you use the COUNTIF function to count numbers with leading zeros, but it will remove those leading zeros, it means that 1289 and 001289 are the same strings.
So how to preserve the leading zeros while counting numbers. And you can use another Excel function named as SUMPRODUCT.
For example, you want to count strings with leading zeros in the range A2:A6, you can write down the following formula based on the SUMPRODUCT function.
=SUMPRODUCT(--($A$2:$A$6=A2))You can enter this formula into Cell B2, then press Enter key. Then you can drag AutoFill Handle down to other cells to apply this formula.
You can also use another excel array formula to count strings with leading zeros as follows:
=SUM(IF($A$2:$A$10=A2,1,0))
2. Count Numbers with Leading Zeros using User Defined Function with VBA Code
Step1: press ALT+F11 to open the VBA editor.
Step2: Click on Insert -> Module to create a new module.
Step3: Copy and paste the following code into the module:
Function CountLeadingZeros_ExcelHow(cell As Range) As Long
Dim count As Long
Dim value As String
value = cell.value
count = 0
While Left(value, 1) = "0" And Len(value) > 1
count = count + 1
value = Mid(value, 2)
Wend
If Left(value, 1) = "0" Then count = count + 1
CountLeadingZeros_ExcelHow = count
End Function
The VBA code uses a While loop to count the number of leading zero characters in the specified cell. And it checks whether the leftmost character of the cell’s value is “0” and whether the length of the cell’s value is greater than 1.
If both conditions are true, it increments a count variable and removes the leftmost character from the cell’s value using the Mid function.
Step4: Save the module and go back to your Excel worksheet.
Step5: Type the following formula in a blank cell to count the number of leading zero characters in cell A2.
=CountLeadingZeros_ExcelHow(A2)Step6: Press Enter to see the result.
3. Video: Count Numbers with Leading Zeros
This video explains how to count numbers with leading zeros in Excel using a formula or VBA code. And it provides step-by-step instructions for both methods and demonstrates how to use them in practice.
4. Related Functions
- Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)… - Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)… - Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…. - Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
5. Related Posts
- How to insert leading zeros to number or text
Assuming that you want to insert leading zeros to number in Cell A2, then you can try to use the TEXT function to create an excel formula. You can also use the CONCATENATE function to add the specific digit of leading zeros into each number… - How to keep or remove leading zeros
If you want to keep the leading zeros as you type in a cell, you need to change the cell format to Text before you type the zip code number.You can remove or delete leading zeros in front of the cells with excel VBA macro… - Count the number of words in a cell
If you want to count the number of words in a single cell, you can create an excel formula based on the IF function, the LEN function, the TRIM function and the SUBSTITUTE function. ..
