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.

Table of Contents

## 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))`

**Note: **when you enter this formula into Cell B2, you still need to press **Ctrl+Shift+Enter** keys to convert this formula as Array formula.

## 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. ..

## Leave a Reply

You must be logged in to post a comment.