Count of text in cells

[Trip Bee]

I have problem to solve,I have a column with office. The text in the Office cells with be, Office 1@810@NT but I now have a new columm with text in the following format:Test The Cell - Employment cells can contain mutiple values, Test The Cell - Education;Test The Cell - Employment;Test The Cell - Health
. These values are separated by a ;
NOt only do I need to count each occurance, but I then need to have a count of each one. I can have these values referred to in a cell if that makes it easier. Sorry if I have not been clear


Submitted via EggHeadCafe - Software Developer Portal of Choice
The SQLite Database Engine and ADO.NET Provider
http://www.eggheadcafe.com/tutorial...8-dc79824ea55f/the-sqlite-database-engin.aspx

 

[joel]

This should work where abc is you search string and A1:A100 is you
column

=SUMPRODUCT(--(ISERROR(FIND("abc",A1:A100))=FALSE))


Find will return an error if the string is not found. You want to coun
the number of occurances that are NOT Error

 

[Trip Bee]

Hi Joel, thanks for your response,

The only problem with this, is that a cell can contain "abc" and other text within the cell. Example:

Cell A1, can contain the following, abc;somthingelse;abc;again somthing else;abc

The formula you supplied will only count "abc" once in this cell. The result in the examble above you be 3.



joel wrote:

This should work where abc is you search string and A1:A100 is
15-Jan-10

This should work where abc is you search string and A1:A100 is you
colum

=SUMPRODUCT(--(ISERROR(FIND("abc",A1:A100))=FALSE)

Find will return an error if the string is not found. You want to coun
the number of occurances that are NOT Error

-
joe
-----------------------------------------------------------------------
joel's Profile: 22
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=17038

Microsoft Office Help

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Programmer's Reference [Wrox Press]
http://www.eggheadcafe.com/tutorial...897-012dfdc39b86/aspnet-programmers-refe.aspx

 

[joel]

I went to Chip Pearson's webpage and found a solution using th
worksheetfunction REPLACE but it only worked on one cell.

'String Formulas' (http://www.cpearson.com/Excel/stringformulas.aspx)

=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1))

The function wouldn't work with a formula array. So it gave me th
idea to use a UDF function. Call thsi function like this


=CountString(A1:A100,"abc")

or

=CountString(A1:A100,bi)


Function CountString(Target, Pattern)

CountString = 0
For Each Cell In Target
MyString = Cell
Do While InStr(MyString, Pattern) > 0
CountString = CountString + 1
MyString = Replace(expression:=MyString, _
Find:=Pattern, Replace:="", Start:=1, Count:=1)
Loop
Next Cell

End Function


The function keeps on replacing the search string with nothing unti
the string isn't found any more counting the number of times th
replaement is done