Excel Cell Value count [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
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
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
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"
Thanks Venkat.My requirement was column will have values like this " 111,222,333" and other column will have "444,555,333".I need to count how many 333 available in the two columns!!!!???

Is there a way ? Can you or any can help me ?
> Dinesh
Is there way to get the values from cell with delimeter?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!