how to count the number of commas, ampisands, etc in a single cell

[palaeontology]

Hi, I'm playing around with len(substitute...) etc to find a way to count the total number of the following symbols or words in a single cell ...

commas
ampisands ... ie: &
plus
lower case x
upper case X
the word .. featuring
the word feats
the word feat

as it's more than 7 things I'm looking for, I can't do a string of nested ifs.

Is there a way to count these items in a single cell ?

Kind regards,

Chris

 

[DanteAmor]

To count comma or & :

varios 29feb2020.xlsm
	A	B
1
2	Hi, I'm playing around, etc to find a way to count the total number of the ,	3
3	data & data & other	2
Hoja5

Cell Formulas
Range	Formula
B2	B2	=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))
B3	B3	=LEN(A3)-LEN(SUBSTITUTE(A3,"&",""))

 

[palaeontology]

Hi Dante, thankyou for putting that together, that's actually what I already was using, but had hoped to make a single formula that looks for 8 or more particular symbols or words in the cell, but it looks like I might have to just write a single formula per symbol that I'm looking for.

On a different, but related note, is there a way (maybe using indirect) to count the number of times the symbol in let's say cell B1, appears in the cell A2 ... so instead of typing the comma into the old text part of the substitute formula, it refers to the comma symbol that sits in B1 ... that way I can change the symbol in B1 (when I want to) to an different symbol and the formula counts the number of times that newly typed symbol appears in A2.

 

[DanteAmor]

Try

varios 29feb2020.xlsm
	A	B
1		&
2	data & data & other	2
Hoja5

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

 

[DanteAmor]

but had hoped to make a single formula that looks for 8 or more particular symbols or words in the cell, but it looks like I might have to just write a single formula per symbol that I'm looking for.

You can give an example of what you have and the result or results that you need.