Counting special characters inside a cell.

[devnullish]

Hey all,

I need to count the number of times a character appears in each cell. The character I need to count is the 'pipe' character. Here it is:

|

I'm trying this:

=LEN(A1)-LEN(SUBSTITUTE(G2,"|",""))

and this:

=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(G2,"|",""))

and neither seems to work.

Any help is greatly appreciated.

[Ron Coderre]

RE:

=LEN(A1)-LEN(SUBSTITUTE(G2,"|",""))
=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(G2,"|",""))

Both of your formula are referencing two different cells:
A1 or B1 in the first LEN calc...then G2 in the second LEN calc.

Did you intend to reference either A1 or G2 both times, instead:
Example:

=LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))
=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"|",""))

[devnullish]

Whoops, thanks for pointing that out!

I found those examples by Googling, and I didn't pay close enough attention when I copied + pasted into the forum here. All cell references should've read G2, which is my starting point in the workbook.

Really what I need to do is count the number of times "|" occurs in each cell, then add 1 to that result so that I know the number of items contained in each cell. The "|" is being used to separate different items in a single cell. Unfortunately for what I'm doing, this is a necessity.