Checking if a cell contains a string that is not case sensitive

[Colin Hempsey]

I am trying to count how many times a specific string "abc" forms part of a
cell in a spreadsheet.

For example

abcgfd, abcdse, efgabc would be vaild and I would like to count these

abdcef, bcadef, cbaeds would not be valid and I would not want to count these.

Can anyone help point me in the right direction?

Thanks.

 

[Joel]

You need to use find with the lookat:=xlpart. See VBA help "FindNext Method"

 

[Don Guillett]

One way (checks for any case also)

Sub countpart()
For Each c In Range("g2:g22")
If InStr(UCase(c), "ABC") Then ctr = ctr + 1
'If InStr(c, "abc") Then ctr = ctr + 1'for abc ONLY (no case check)
Next c
MsgBox ctr
End Sub

 

[Rick Rothstein \(MVP - VB\)]

This count you are doing... it is in a single cell, right? If so, this
function should do what you want...

Function CountTextInCell(CellText As String, TextToCount As String) As Long
If Len(CellText) = 0 Then Exit Function
CountTextInCell = (Len(CellText) - Len(Replace(CellText, TextToCount, _
"", , , vbTextCompare))) / Len(TextToCount)
End Function

Just pass in the text from the cell. For example...

Sub Test()
MsgBox CountTextInCell(Sheet2.Range("A1").Value, "abc")
End Sub

Rick