counting number of special character #

  • Thread starter Thread starter james
  • Start date Start date
J

james

Could any one tell me how to count number of special
character # in a range of cells.

I have used the formular =SUM(LEN(range)-LEN(SUBSTITUTE
(range,"#",""))). The formular does not work with the
character #.

Could any one help.

james
 
James.

If it is # with other chars in the cell then try
=SUM(LEN(range)-LEN(SUBSTITUTE(range,"#","")))
which is an array formula, so enter with Ctrl-Shift-Enter
 
Hello Alan,

The # is at the beginning of a string. I got zero in
return using countif(range,"#"). Do you have any other
solution.

Thanks again.

James
 
=SUM(LEN(H16:H19)-LEN(SUBSTITUTE(H16:H19,"#","")))

worked fine for me if I entered it with Ctrl+Shift+Enter rather than just
enter.

or

=SUMPRODUCT(LEN(H16:H19)-LEN(SUBSTITUTE(H16:H19,"#","")))

entered normally.
 
Sure. =COUNTIF(range,"*#*").
It counts the number of cells with one or more #'s in them.

Alan Beban
 
Thanks for the feedback, James. The formula I provided will count only
the number of cells with one or more #; so if a cell has two #'s, it
will count them as 1. Tom Ogilvy has provided a formula that will count
the number of #'s.

Alan Beban
 
Back
Top