Count occurrences of character in cell

peter_sjogarde

Board Regular
Joined
Feb 13, 2012
Messages
56
I would like to count the occurrences of a character in a cell.

Example:

A1 contains "name; name; name"

In B1 I want to count the occurences of ";" and add with 1 to get the number of persons listed in A.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Perhaps this Function

Code:
Option Explicit
 
Public Function CountChar(InputRange As Range, CharToCount As String) As Long
 
    Dim i As Long, myCount As Long
    myCount = 0
 
    For i = 1 To Len(InputRange.Value)
        If Mid(InputRange.Value, i, 1) = CharToCount Then myCount = myCount + 1
    Next i
 
    CountChar = myCount
 
End Function

Used like:

Excel Workbook
AB
20name; name; name2
Sheet3
Excel 2007
Cell Formulas
RangeFormula
B20=countchar(A20, ";")



Regards
Adam
 
Upvote 0
I can not find the countchar function. Is it some kind of plugin? Differences in versions (I got excel2010)?

Vog's solution is much more elegant than mine. You should use that.

For info only though - the code I posted in the same post needs to be coppied in to a module in the VB editor: Press ALT+F11, Right Click on 'VBA Project()' and Insert -> Module. Copy in the code. You can then close this window and use the CountChar function in this workbook.

Regards
Adam
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top