0

I'm looking for a way to find an exact string of text within a range of cells so I can count the number of occurrences of the substring. By exact, I mean that if the range contains multiple occurrences of the values "apple" and "apples", and I want to count the number of occurrences of "apple", I don't want "apples" included. I've tried this with COUNTIF and SUM(LEN/SUBSTITUTE) formulas, but they return counts for "apple" and "apples" since "apples" contains the string "apple". Thoughts?

user3597506
  • 3
  • 1
  • Is there a single word in the cells or phrases in the cells?? – Gary's Student May 02 '14 at 20:48
  • sorry but you are wrong. COUNTIF(yourRange,"apple") would only count cells that exactly are "apple", not "apples". Perhaps if you elaborate on your data / layout it we may be able to work out where the actual issue is. – Cor_Blimey May 02 '14 at 21:09
  • Thanks. Should have clarified in the initial post: each cell in the range can contain multiple, comma-separated values; e.g., "banana, apple, orange, apples". So, I am essentially searching for the text "apple" in each cell and, therefore, can get a positive return when using the previously mentioned functions when the cell/range contains "apples". – user3597506 May 02 '14 at 21:40

1 Answers1

0

Try this little User Defined Function ( UDF )

Public Function FruitCounter(rIn As Range, s As String) As Long
    Dim r As Range
    Dim ary() As String
    For Each r In rIn
        ary = Split(Replace(r.Value, " ", ""), ",")
        For i = LBound(ary) To UBound(ary)
            If ary(i) = s Then
                FruitCounter = FruitCounter + 1
                GoTo ext1
            End If
        Next i
ext1:
    Next r
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=FruitCounter(A1:A10,"apple")

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

Community
  • 1
  • 1
Gary's Student
  • 95,722
  • 10
  • 59
  • 99