Count number of caracters in Text

[hilcom]

Hello anyone,

I have a large text with a number of comma's in it. (i.e content of A1 = M1,2,3,4,5,6,aaa,bb,cc)

How can I count the number of comma's (thought NOT the number of total caracters of the text, but how many times is there a comma (of whatever caracter) in the text?)

Of course I can import the text in Excel, but I need a vb code to do so.

Regards

Bob, NL

 

[Faseeh]

Hi hilcom,


If your data is in cell A1, try this:

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

Regards,

Faseeh

 

[hilcom]

sorry, this does not work on my test

in my example there are 8 comma's. I need that number (8)

subsitute in the format you write does not work in muy Excel version (20120)

Bob

 

[Luke M]

Faseeh's formula should be working. Can you explain further what you mean by "does not work"? This could mean serval things...formula showed an error, formula showed a different number than expecting, formula won't let you confirm, etc.


Why does the function not work? What does the error message, if there is one, say?

 

[hilcom]

Sorry, I took the wrong Dutch version of Substitute. Everything is fine. THANKS!!!!

 

[Luke M]

No worries, glad you got it figured out.