# Excel Cell Value count

[Closed]
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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

## 2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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")