Re: Excel 2007 - Count of certain Letter within a cell's text string

[threefifths]

Hi,

I was reading this post on how to count the number of occurrences of a word in a range of cells and came across an issue. Here's the formula that was recommended, which works well under certain circumstances.

=SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)

If I want to search for a single word "a" in a cell with the following content, using a version of this formula (I've attached an example sheet), it returns a value of 5: "This text contains no single character but it does have the letter I'm searching for."

The answer I'm looking for is 0, but as the letter "a" appears 5 times it counts them all. Is there any way to specify it took look for the exact contents of the cell?

Cheers,

3/5

[Ron Coderre]

1) I moved your question into a new thread, as it really is a different issue.
2) What are you looking for in your example?
...Testing if the cell ONLY contains the letter "a"

...Testing if the cell contains the letter "a" as a standalone "word"?
"This cell contains a single instance of a standalone-a"

...Counting how many standalone a's the cell contains?
"This is a cell that contains a standalone-a three times"

[threefifths]

Hi Ron,

Thanks for the response and setting me up with a new thread. To further clarify, I think this best describes what I want:
. ...Counting how many standalone a's the cell contains?

Here's a better example of the issue.
If I had a single cell with this text in it:

"I have a toy car. The car is blue. It came in a cardboard box. It's from the cartoon movie Cars."

I would like to count the exact word car, so hopefully the formula would return a value of 2. Right now, using the formula I specified in the first post. I get a value of 5 because it has a plural version and "car" appears in 2 other words.

I think that formula can probably do the job, I'm just not sure how to specify exact matching. A colleague suggested I add some conditions, (space)car, (space)car(space) and car(space) to match it exactly, but it seems like there should be an easier way.

Cheers,

3/5

[DonkeyOte]

Your friend's suggestion is correct, however, how viable that formula based approach is depends on the consistency of delimiters.

In your example you have 2 delimiters, space & full stop. You would need to replace the full stops and for safety replace with a space such that you have common delimiters throughout
(note the replace with space is not essential given the example and consistency of punctuation - ie space follows full stop) .

result:
=SUMPRODUCT((LEN(" "&SUBSTITUTE(D1:D10,"."," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(UPPER(D1:D10),"."," ")&" "," "&UPPER(A1)&" ",""))))/LEN(" "&UPPER(A1)&" ")

If you have mixed punctuation the above will prove to be a cumbersome approach and you would be best served reverting to a User Defined Function [VBA] perhaps incorporating a regular expression approach...

[Ron Coderre]

If a User Defined Function (using VBA) is OK...then

• ALT+F11....to open the VBA editor
• Insert.Module
• Paste the below code into that module:

Public Function CountMatches( _
    strText As String, Phrase2Match As String, Optional MatchCase As Boolean)
Dim RegExp As Object
Dim regexpPattern As String
Set RegExp = CreateObject("vbscript.regexp")
If MatchCase <> True Then
    strText = UCase(strText)
    Phrase2Match = UCase(Phrase2Match)
End If
regexpPattern = "\b" & Phrase2Match & "\b"
RegExp.Pattern = regexpPattern
RegExp.Global = True
CountMatches = (Len(strText) - Len(RegExp.Replace(strText, ""))) / Len(Phrase2Match)
End Function

To use that function for text in A1...
B1: =countmatches(A1,"Car",TRUE)

A1 refers to a range or text within quotes
"Car" is the text to count
TRUE sets the case-sensitivity
...TRUE: means match the case of the text to count
...FALSE or omitted means the match is case-insensitive

Is that something you can work with?

[threefifths]

Wow. Thanks to all for the help. It's a bit more advanced than I was expecting. I'm going to have to test them all and try to understand what's going on, I'm a bit of a novice at UDFs but this gives me a great excuse to learn more.

I've tried DonkeyOte's approach but I can see that it will not hold up under all possible circumstances. Thanks for the input though, it was interesting to see your approach.

I'll post a follow up regarding Ron Coderre and Marcol UDFs once I've had a chance to play around with them.

I'll mark the thread as solved.

Thanks again for the help and suggestions.

Cheers,

3/5

[Marcol]

Try this UDF

Function CountWord(rng As Range, strWord As String, Optional CaseSensitive As Boolean = False)
    Dim n As Long, ctr As Long
    Dim strTemp As String, strLeft As String, strRight As String

    If CaseSensitive Then
        strTemp = rng
        strWord = strWord
    Else
        strTemp = UCase(rng)
        strWord = UCase(strWord)
    End If
    If InStr(1, strTemp, strWord) = 1 Then strTemp = " " & strTemp
    
    ctr = (Len(strTemp) - Len(WorksheetFunction.Substitute(strTemp, strWord, ""))) / Len(strWord)

    For n = 1 To ctr
        strLeft = UCase(Mid(strTemp, InStr(1, strTemp, strWord) - 1, 1))
        strRight = UCase(Mid(strTemp, InStr(1, strTemp, strWord) + Len(strWord), 1))
        If ((Asc(strLeft) < 65 Or Asc(strLeft) > 90) And Not IsNumeric(strLeft)) _
           And ((Asc(strRight) < 65 Or Asc(strRight) > 90) And Not IsNumeric(strRight)) Then
            CountWord = CountWord + 1
        End If
        strTemp = Mid(strTemp, InStr(1, strTemp, strWord) + Len(strWord) + 1, 255)
        If InStr(1, strTemp, strWord) = 1 Then strTemp = " " & strTemp
    Next

End Function

Use like this

=CountWord(A3,$B$1)

To make count case sensitive

=CountWord(A3,$B$1,TRUE)

This will count "(car)", "/car/", "car.", etc as instances of "car" and ignore "cartoon", "scarred", etc.
I have put a few options to search for in a dropdown in B1

I have not got the time just now to test this thoroughly, you might want to add some more conditions.

Hopefully this might give you a start for a UDF that meets your criteria.