Counting for a string in a single cell
Posted by Altug Bayram on October 23, 2001 8:15 AM
Hi,
I am trying to count a specific string in a SINGLE cell
for the number of occurences. So for instance if the cell
contains the following
"0-R-0, 1-R-0, 3-C-0" etc ...
and if I am trying to count number of occurences of "-R" within
that single cell (the answer for the above should be 2)
Once I know how to do that for a single cell, I would
then need to apply this for a range of cells that have
the same issue. It is a lot of data and I don't like
to use "Text to Columns" for this case. For one
reason, the data sometimes is not well separated (within
the same cell). I am really looking for almost like
SEARCH function but it should be able to count for
all occurences of a string within the same cell.
Any help will be greatly appreciated. Thanks a lot.
Posted by Aladin Akyurek on October 23, 2001 8:26 AM
Altug --
=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER("B1),"")))/LEN(B1))
where B1 contains the substring whose occurrences you want to count and A1 contains the string in which to look.
Aladin
==========
Posted by Juan Pablo on October 23, 2001 8:28 AM
Try using this Array - Formula (To enter it press Control Shift Enter)
=SUM((MID(A1,ROW(INDIRECT("1:"&(LEN(A1)-1))),2)="-R")+0)
It assumes that your text is in A1.
Juan Pablo
Posted by Aladin Akyurek on October 23, 2001 8:29 AM
That's too heavy, modified from a formula that applies to a range. So use instead:
(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER("B1),"")))/LEN(B1)
Aladin
========
Posted by Aladin Akyurek on October 23, 2001 8:32 AM
Juan -- That's heavy-handed too, see my post.
Aladin
=========
Posted by Juan Pablo on October 23, 2001 8:33 AM
I saw it, and i agree, it's heavy... i was thinking checking pairs of characters, yours is easier, faster, and "lighter"...
Thanks
Juan Pablo
Posted by Altug Bayram on October 23, 2001 12:36 PM
Aladin and Juan,
Thank you very much, both of your suggestions worked
fine. I really appreciate this help, I think this
board is the heaven for excel oriented people. Again,
many thanks to both of you.
Altug
