How to Count Numbers with Leading Zeros

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.

count string with leading zero1

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))
count string with leading zero2

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.

How to Count Numbers with Leading Zeros vba 2.png

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

Check if Cell Contains Certain Values but do not Contain Others Values

In the previous post, we only talked that how to check a cell if contains one of several values from a range in excel. And this post explains that how to check a cell if it contains certain values or contains one of several values but do not contain other certain values in another range or a list in excel.

Check if Cell Contains Certain Values but do not Contain Others Values

Supposing that you have a list of text strings in the range B1:B3 and you need to check each Cell that if it contains one of several values in a range D2:D4 but do not contains any of values in range E2:E3.  If TRUE, then returns TRUE value, otherwise, returns FALSE value.

To check a cell to see if it contains certain string but do not contain others. You can use a combination of the AND function, the COUNT function and the SEARCH function to create a new array formula as follows:

=AND(COUNT(SEARCH($D$2:$D$4,B1))>0, COUNT(SEARCH($E$2:$E$3,B1))=0 )

Let’s see how this formula works:

= SEARCH($D$2:$D$4,B1)

The SEACH function returns position of the first character of find_text in a text string. And this formula will search each value in the range D2:D4 inside within_text in Cell B1, then returns position of each text string in Cell B1, so it will return an array result like this:

{1;7;12}

 

= SEARCH($E$2:$E$3,B1)

This SEARCH formula will search each value in range E2:E3 inside within_text in Cell B1, and then returns position of the first position of each find_text in Cell B1. When no match is found, the SEARCH function will return the #VALUE error. So it will return an array result like this:

{#VALUE!;#VALUE!;1}

 

=COUNT(SEARCH($D$2:$D$4,B1))>0

The COUNT function will count the number of cells that contain numbers in an array returned by the SEARCH function. If it is greater than 0, then it indicated that at least one values is found in Cell B1.

 

=COUNT(SEARCH($E$2:$E$4,B1))=0

If the number of cells that contain numbers is equal to 0, then it means that none of values can be found in Cell B1.

 

=AND(COUNT(SEARCH($D$2:$D$4,B1))>0, COUNT(SEARCH($E$2:$E$4,B1))=0 )

If both two COUNT function return TRUE value, the AND function returns TRUE. And if either COUNT function is FALSE, this formula returns FALSE.

contains certain values but do not contain1


Related Formulas

Related Functions

  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel COUNT function
    The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…

 

How to get first match that cell contains one of several values in a range

This post explains that how to get the first match that cell contains one of several values from another range or a list in excel. before we talked the way that how to check a cell to see that if it contains one of several values in a range or list using the SEARCH function with ISNUMBER function. So now we still need to get the first match that found in the range or list.

Get First Match that Cell Contains one of several values

Assuming that you have a list of text strings in the range B1:B3 and you want to check each text string if it contains one of several values from the range E1:E3. If it contains any of text string in range E1:E3, then it should return the first match value found in the range E1:E3, otherwise, it should return the #N/A error. you can use a combination of the INDEX function, the MATCH function, the ISNUMBER function and the SEARCH function to create a new excel array formula as follows:

=INDEX($E$1:$E$3, MATCH(TRUE, ISNUMBER( SEARCH($E$1:$E$3,B1)),0))

Let’s see how this formula works:

=SEARCH($E$1:$E$3,B1)

The SEACH function returns position of the first character of find_text in a text string. And this formula will search each value in the range E1:E3 inside within_text in Cell B1, if it found the match, then returns position of each text string in Cell B1, otherwise, it will return the #VALUE! Error. So it will return an array result like this:

{1;7;12}

The returned result goes into the ISNUMBER function as its argument.

 

=ISNUMBER(SEARCH($E$1:$E$3,B1))

The ISNUMBER function will check if a cell contains a numeric value, and this formula will check each items of array result returned by the SEACH function, if the item is a numeric value, then return TRUE, otherwise, returns FALSE. So it will return another array result like this:

{TRUE;TRUE;TRUE}

 

=MATCH(TRUE, ISNUMBER( SEARCH($E$1:$E$3,B1)),0)

get first match1

The MATCH function will search the first value that is exactly equal to the lookup_value “TRUE”, then return the position of the first match. So this formula returns 1. It goes into the INDEX function as its row_num argument.

 

=INDEX($E$1:$E$3, MATCH(TRUE, ISNUMBER( SEARCH($E$1:$E$3,B1)),0))

get first match2

The index function will extract a value from the range E1:E3 based on the position number returned by the MATCH function. So it should be returned the first match value as “excel”.


Related Formulas

  • Check If Cell Contains All Values from Range
    If you want to check if a cell contains all values in a list, you can use a combination of the SEARCH function, the SUMPRODUCT function, the ISNUMBER function and COUNTA function…
  • Get last match that cell contains one of several values in a range
    If you want to check a cell that if it contains one of several values in a range, you can use the SEARCH function to search find_text in a range inside a text string. Then we can use the LOOKUP function to get the last match values….
  • Check if Cell contains one of many values from range
    Assuming that you have a list of text strings in the range B1:B3 and you want to check each text string if it contains one of several values in a range E1:E3. If it contains any of text string in range E1:E3, then it should be return TRUE value, otherwise, it should return FALSE….

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

 

How to Split Text String to an Array

This post explains that how to convert the text to an Array in excel. How to convert text into array using formula in excel 2013? How to split the text string into an array where the each character in text is an element in array using excel formula in excel 2013. How to split text into an array using user defined function in VBA.

How to split text string into an Array with excel formula

If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string in Cell B1 into an array in Cell C1. Refer to the below formula:

{=MID (B1, ROW ( INDIRECT ("1:" &LEN (B1))),1 )}

Note: the above formula is an array formula, so when you enter it in Cell Ce, you should enter the shortcut “CTRL”+”SHIFT” +”enter” to indicate that it is an array formula.

Let’s see how the above formula works:

Assuming that the length of the Cell B1 is 5.

=LEN(B1)

How to split text string into an Array with excel formula1

The LEN function returns the length of a text string in Cell B1. In other words, you will get the numbers of characters in Cell B1.

 

=”1:”&LEN(B1)

How to split text string into an Array with excel formula1

The above formula will join the text string between “1:” and the result returned by the LEN function using ampersand operator. it will return a text string as : “1:5”

 

=INDIRECT(“1:”&LEN(B1))

The INDIRECT returns a cell reference from a specified text string. So it will return the reference from row 1 to row5.

 

=ROW(INDIRECT(“1:”&LEN(B1)))

How to split text string into an Array with excel formula3

You can press “F9” to check the actual element of array returned by the ROW function.

How to split text string into an Array with excel formula3

The ROW function will return the row number based on the row references returned by the above INDIRECT function. so it will return the below array( each row number is an element in array).

{1;2;3;4;5}

 

=MID(B1, ROW(INDIRECT(“1:”&LEN(B1))),1 )

How to split text string into an Array with excel formula3

You can select the above formula, then press “F9” to get the actual array result.

How to split text string into an Array with excel formula3

The MID function will extract sub strings based on each start_num in array element returned by the above ROW function and the number of character to extract is always 1. So the above MID function will return the below string array in array formula.

{"e";"x";"c";"e;";"l"}

 

How to split text string into an Array with VBA code

You can also convert the text string to array using a user defined function in VBA. You can follow the below steps to create a new Excel function to split text string into an array in Excel VBA:

1# click on “Visual Basic” command under DEVELOPER Tab.

How to split text string into an Array with VBA code2

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module named as: CharToArray1

How to split text string into an Array with VBA code2

4# paste the below VBA code into the code window. Then clicking “Save” button.

How to split text string into an Array with VBA code2

Function CharToArray(value As String)
    value = StrConv(value, vbUnicode)
    CharacterArray = Split(Left(value, Len(value) - 1), vbNullChar)
End Function

5# back to the current workbook, then enter the below formula in Cell C1:

=CharToArray(B1)

How to split text string into an Array with VBA code4


Related Formulas

  • Combine Text from Two or More Cells into One Cell
    If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…
  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…

Related Functions

  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel INDIRECT  function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…