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.

Zillmere, QLD, 4304
Westfield Belconnen, Benjamin Way, Belconnen, ACT, 2617
Wanneroo Central Shopping Centre, Rocca Way, Wanneroo, WA, 6065
Unit 6, 5 Hughes Street, (cnr Nicholson Road & Hughes Street), Canning Vale, WA, 6155

<tbody>
</tbody>

Desired Result

Zillmere, QLD, 4304 <tbody> </tbody>	2
Westfield Belconnen, Benjamin Way, Belconnen, ACT, 2617 <tbody> </tbody>	4
Wanneroo Central Shopping Centre, Rocca Way, Wanneroo, WA, 6065 <tbody> </tbody>	4
Unit 6, 5 Hughes Street, (cnr Nicholson Road & Hughes Street), Canning Vale, WA, 6155 <tbody> </tbody>	5

<tbody>
</tbody>

 

[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.