Excel Cell Value count Closed

Question

I have questions

Can Cell determined by delimeter ?

I want to count the cell values how many 111 occurs ? pls refer the below example

ColumA	Column B
111,222,333	222,333,444
666,333,111

venkat1926 · Answer

I do not kow whether there are horizontal (in one row) A1 to G1 or in 7 rows from A1 to A7

if its former 
=COUNTIF(A1:G1,"111")
will give you 2
 if itis vertical 
=COUNTIF(A1:A7,"111")

read excel help on "countif"

venkat1926 · Answer

supposse the entries are in A1 and B1 try this formula 

=(LEN(A1)-LEN(SUBSTITUTE(A1,"333",""))+LEN(B1)-LEN(SUBSTITUTE(B1,"333","")))/LEN("333")

now if there are more cells then it would be ccumbrsome.  in that case use a macro 

the  macro is like this 

Sub test()
Dim j As Integer, rng As Range, c As Range
j = 0
Set rng = Range(Range("a1"), Range("a1").End(xlToRight))
For Each c In rng
j = j + UBound(Split(c, "333", , 1))
Next c
MsgBox j

End Sub


This was suggested recently by Peter_SSs  an expert in one of the newsgroups

Excel Cell Value count

2 responses

Similar discussions

Subscribe To Our Newsletter!