How to count instances of a character within a cel (2000/2003/2007)

Topic

New Reply

I tried searching on this but did not find any usseful links until after I came up with the solution on my own and even then I found only 1 link on Google for this.

Question: How to count the instances of a character within a cell.

Answer: While there are many tips on the net on how to count the number of cells meeting some crtieria, counting the number of instaces of a character in a cell is a different story. The following formula can be used to do just this. While I did come up with the below on my own, afterwards I found a link on Google where someone else had al;ready come up with the same formula. SO while I did come up with this on my own I was not the first to do so and therefore do not take credit for originating it. I’m hoping posting this here will help others more easily find this should they need it.

In the below formula,
N represents the character you are searching for and cell A1 contains text which has 1 or more instances of ‘N’.

=Len(A1) – Len(Substitute(A1,N,””)

A more detailed example, lets assume that cell A1 contains the text “Hello World” and you want to count how many instances of the letter ‘l’ are in the cell. The formula would look like this:

=Len(A1) – Len(Substitute(A1,”l”,””)

This formula would return the value 3. You can also use this formula to count the number of special/non-printing characters are in a cell. For example to count how many instaces of the non-printing character CHAR(160) are in cell A1 you would use the following formula:

=Len(A1) – Len(Substitute(A1,CHAR(160),””)

Ed

Reply | Quote

Replies

Hi Ed

Very useful , you are however missing the last parenthesis “)” on the 3 formulas to work properly

Reply | Quote

Jezaa,

ARRRRGGGHHHH( done in the sound of the typical South Atlantic Pirate-nese) you be right about that blatsed paren. I guess it walked it the plank

Ed

Reply | Quote

This is a relatively common use of substitute and you can find many instances on this board by searching len substitute.

A more generic one is:

=Len(A1) – Len(Substitute(A1,N,””))/Len(N)

in case the substring you are searching is more than one letter. In VB you must use something like:

iCount = (Len(sTemp) – len(Application.worksheetfunction.substitute(sTemp, str, “”))) / len(str)

since the substitute is not part of VB.

HansV also used it in an array formula to [Count instances of a letter in a range of cells (2003) instead of a single cell.

Steve

Reply | Quote

Steve,
The use of len substitue does help locate many examples of this however you have to know part of the solution to know to use these keywords. I added the post with the specific wording I did so as to make it easier for others to find this same answer. It seems like too often the right keywords aren’t located with a piece of info unless it is in response to a question from another as is the case with these forums.

Thanks
Ed

Reply | Quote