Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

counting number of special character #

29 views
Skip to first unread message

james

unread,
Aug 29, 2003, 1:39:41 PM8/29/03
to
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

Alan Beban

unread,
Aug 29, 2003, 1:55:29 PM8/29/03
to
=COUNTIF(range,"#")

Alan Beban

james

unread,
Aug 29, 2003, 2:21:19 PM8/29/03
to
thanks aln,
the # is first character of a string. using =countif
(range,"#"), i got zero in return.

james

>.
>

Bob Phillips

unread,
Aug 29, 2003, 2:29:07 PM8/29/03
to
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

--

HTH

Bob Phillips

"james" <jj...@sleh.com> wrote in message
news:096801c36e5a$5805c130$a001...@phx.gbl...

JAMES

unread,
Aug 29, 2003, 2:33:27 PM8/29/03
to
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

>.
>

James

unread,
Aug 29, 2003, 2:44:14 PM8/29/03
to
Hello Bob,

I used your method but I got message of "#Value!".

James

>.
>

Tom Ogilvy

unread,
Aug 29, 2003, 2:56:38 PM8/29/03
to
=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.

--
Regards,
Tom Ogilvy

"james" <jj...@sleh.com> wrote in message

news:033501c36e54$871626f0$a101...@phx.gbl...

Alan Beban

unread,
Aug 29, 2003, 3:38:23 PM8/29/03
to
Sure. =COUNTIF(range,"*#*").
It counts the number of cells with one or more #'s in them.

Alan Beban

Alan Beban

unread,
Aug 29, 2003, 3:51:13 PM8/29/03
to
There seems to be some confusion (at least on my part) as to whether
your cells have more than one # in them, and if they do, what you want
to count.

Alan Beban

james

unread,
Aug 29, 2003, 4:27:19 PM8/29/03
to
hello alan,

thanks for your help. the formular =countif(range,"*#*")
works great. i do have two strings in one cell with each
with a # character.

thanks again.

james

>.
>

Alan Beban

unread,
Aug 29, 2003, 5:07:54 PM8/29/03
to
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

0 new messages