How to Count Cells that End with Specific Text in Excel

This post will guide you how to count the number of cells that end with a specific value within a range of cells using a formula in Excel 2013/2016 or Excel office 365. How do I count cells that end with a specific text through the use of VBA Macro in Excel.

Count Number of Cells that End with a Specific Text Through Formula


If you want to count the number of cells that end with a specific text value “el” in range B1:B6, and you can use the COUNTIF function to build a formula. And you need to use the asterisk(*) sign to count the number of cells that end with “el”. Just using the following formula:

=COUNTIF(B1:B6,”*ex”)

count cells that end with 1

LET’S SEE THAT HOW THIS FORMULA WORKS:

The COUNTIF function can be used to count the number of cells in the range that end with a specific text by matching the content of each cell against the pattern. And the asterisk sign is a wildcard that means “match any number of characters”.

Count Number of Cells that End with a Specific Text Through VBA


You can also use an VBA Macro to count cells if ends with a specific value in Excel. Just do the following steps:

Step1# open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

 Get the position of the nth using excel vba1

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

Step3# click “Insert” ->”Module” to create a new module.

convert column number to letter3

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

count cells that end with 2

Sub CountCellsEndWith()
Dim ws As Worksheet
Set ws = Worksheets("Sheet3")
ws.Range("D1") = Application.WorksheetFunction.CountIf(ws.Range("B1:B6"), "*" & "el")

End Sub

Note: you need to change the output range D1 to place the final result. And change the data range that you want to test the criteria against by changing the cell reference. You also need to change “el” pattern as you need .

Step5# back to the current worksheet, click on Macros button under Code group. then click Run button.

count cells that end with 3

Step6# let’s see the result:

count cells that end with 4

Related Functions


  • 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) …

 

Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the ...

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...

Sidebar