CODEWITHSUNDEEP
excelvba
Josh
Josh

Reputation: 265

Finding multiple counts of phrase in a single cell

I need something counts the number of times a certain phrase presents itself in a SINGLE cell.

Cell B5 contains the value "Test 123 Test 1234"

Sub count()
ival = Application.WorksheetFunction.CountIf(Range("B5"), "Test")
MsgBox ival
End Function

This returns "0" because it's not an exact match. I want something that counts the number of times "Test" is in the cell and properly returns 2 as the value.

Upvotes: 1

Views: 53

Answers (1)

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

Combine Split and Ubound

Split the string into zero based 1D array of substrings, then find the upperbound of that array using ubound

Sub count()
 Dim count As Integer
 count = UBound(Split(Range("B5"), "123"))
 MsgBox count
End Sub

Upvotes: 3

Related Questions