Count Occurrence of a Specific Character in a cell

[Blessy Clara]

Hi

Thank you in Advance,
I have a column of Address, and i would like to know the Number of times, a comma occurs in the cell.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Zillmere, QLD, 4304[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Westfield Belconnen, Benjamin Way, Belconnen, ACT, 2617[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wanneroo Central Shopping Centre, Rocca Way, Wanneroo, WA, 6065[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unit 6, 5 Hughes Street, (cnr Nicholson Road & Hughes Street), Canning Vale, WA, 6155[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Desired Result

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="class: xl63, width: 459"]Zillmere, QLD, 4304[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="class: xl65, width: 459"]Westfield Belconnen, Benjamin Way, Belconnen, ACT, 2617[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="class: xl65, width: 459"]Wanneroo Central Shopping Centre, Rocca Way, Wanneroo, WA, 6065[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][TABLE="width: 459"]
<tbody>[TR]
[TD="class: xl65, width: 459"]Unit 6, 5 Hughes Street, (cnr Nicholson Road & Hughes Street), Canning Vale, WA, 6155[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Excel 2010
	A	B
1	Zillmere, QLD, 4304	2
2	Westfield Belconnen, Benjamin Way, Belconnen, ACT, 2617	4
Sheet1

Cell Formulas
Range	Formula
B1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
B2	=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

 

[Blessy Clara]

Hi Scott T

Thank you very much for the formula, Yes awesome

 

[hiker95]

Blessy Clara,

Here is another formula to try.


If A1 contains Zillmere, QLD, 4304

Then, in B1 enter the following formula:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))


B1 will display 2

 

[Blessy Clara]

Hi hiker95

Yes that was cool, thank you very much for the formula

 

[hiker95]

Blessy Clara,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.