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

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
Fri May 09 06:15:02 CDT 2008

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

"Colin Hempsey" wrote:

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

Don
Fri May 09 07:21:12 CDT 2008

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Colin Hempsey" <ColinHempsey@discussions.microsoft.com> wrote in message
news:3E2E1182-0C1E-4D02-B291-47BEB02A2850@microsoft.com...
>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.

Rick
Fri May 09 11:41:18 CDT 2008

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


"Colin Hempsey" <ColinHempsey@discussions.microsoft.com> wrote in message
news:3E2E1182-0C1E-4D02-B291-47BEB02A2850@microsoft.com...
>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.