Thread: String Frequency Counting in a Single Cell (Excel 2000)

1. String Frequency Counting in a Single Cell (Excel 2000)

I want to know how frequent a text string occurs in another text string.

i.e. CountFrequency (C1, D1) = 9

where cell C1 contains "t" and cell D1 contains "I want to know how many times a text string occurs in another text string."

2. Re: String Frequency Counting in a Single Cell (Excel 2000)

This is limited to a single character, but I can make it work for a string if you like. Create a function in VBA as follows: Open EXCEL, press Alt-F11 to open the VBA editor. Press Ctrl-R to ensure the Project Explorer is open. Single-Left-Click "VBAProject (the name of your workbook)". From the Top Menu, select Insert Module. A module screen will appear. Cut and Paste the following VBA code into the Module Screen:
Function CountFrequency(vchar, vstr)
n = 0
For i = 1 To Len(vstr)
If Mid(vstr, i, 1) = vchar Then n = n + 1
Next i
CountFrequency = n
End Function

Press Alt-F11 to go back to EXCEL. Then enter the following formula into any cell you want: =CountFrequency(C1, D1). I will count the number of times the character occurs. By the way, the answer is 10 times for the letter "t", not 9.

3. Re: String Frequency Counting in a Single Cell (Excel 2000)

Mark,

This worksheet formula ought to work...
=LEN(D1)-LEN(SUBSTITUTE(D1,C1,""))

Regards,
Jim Cone
San Francisco, CA

4. Re: String Frequency Counting in a Single Cell (Excel 2000)

That's sexy. thanks.

I added the "/LEN(C1)" to allow for any length of string.

=(LEN(D2)-LEN(SUBSTITUTE(D2,C2,"")))/LEN(C2)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•