How to count the number of digits in Microsoft Excel

Counting digits in Excel refers to the process of counting the number of digits in a cell or range of cells that contain numbers. It is a common task, used for a variety of purposes, such as analyzing data, formatting cells, or performing calculations.

In this tutorial, we will learn how to count the number of digits in Microsoft Excel. In Excel, we can use the LEN function to count the number of digits if the cell does not contain any characters. However, for counting the number of digits in a cell having numbers as well as characters we can use a combination of the LEN function and the SUBSTITUTE function to count the number of digits.

Method 1: Counting the Number of Digits in a Cell Having Only Numbers

Our dataset contains information about the salaries of various employees. We aim to determine the number of digits in the salary earned by each employee.

Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to count the number of digits.
• Place an Equals sign in the cell.

Step 2 – Enter the LEN Function

• Enter the LEN function in the cell i.e. LEN(B2).
• Where B2 is the cell containing the numbers.

Step 3 – Press the Enter Key

• Press the Enter key to return the number of digits.

Step 4 – Use the Autofill Feature to Count Digits In Each Cell

• Use Autofill to count the number of digits in each cell.

Method 2: Counting the Number of Digits in a Cell Having Numbers and Other Characters 

Here we have random text strings containing both numbers and characters. The formula we will use for this purpose is a combination of LEN and SUBSTITUTE Functions i.e.

SUM(LEN(C5)–LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},)))

 Step 1 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell where you want to count the number of digits.
• Place an Equals sign in the cell.

Step 2 – Enter the SUM Function

• Enter the SUM function in the Cell i.e. SUM(

Step 3 – Enter the First LEN Function

• Enter the first LEN function in the cell i.e. SUM(LEN(A2)
• The first LEN function returns the original length of the cell including numbers and characters. 

Step 4 – Enter the Second LEN Function

• Place a subtraction sign ( – ).
• Enter the second LEN function i.e. SUM(LEN(A2)-LEN(.
• This LEN function will return the length of the output returned by the SUBSTITUTE function.

Step 5 – Enter the SUBSTITUTE Function

• Enter the SUBSTITUTE function as the argument of the second LEN function i.e.

           SUM(LEN(C5)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},)))

• The SUBSTITUTE function substitutes all the numbers from 0 to 9 in the cell.

Step 6 – Press the Enter Key

• Press Enter key to get the number of digits.

Step 7 – Use the Autofill Feature to Count Digits In Each Cell

• Use Autofill to count the number of digits in each cell.

Breakdown of the formula used:

The formula SUM(LEN(C5)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},))) is used to count the total number of digits in a cell (A2) by subtracting the length of the cell after replacing all non-digits with an empty string (using the SUBSTITUTE function), and then summing the result across multiple cells (C5 in this case).

SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},””) replaces all digits in cell A2 with an empty string, effectively removing them from the string.

LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},””)) returns the length of the resulting string, which is the number of non-digit characters in cell A2.

LEN(C5)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},””)) subtracts the number of non-digit characters in cell A2 from the length of cell C5, which is the total length of the original string in A2.

SUM(LEN(C5)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},””))) sums this result across multiple cells to count the total number of digits in a range of cells.