count number of appearences in cell

[Jack Sons]

Hi all,

A certain character or group of characters appears a number of times in the
content of a cell (3a appears two times in d763ah555#3abds3j. With what
formula or code can I count that number? I think I knew it once, but I can't
remember.

Jack Sons
The Netherlands

 

[Trevor Shuttleworth]

Jack

one way:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"3a","")))/2
for a search string which is 2 characters long as in your example

Regards

Trevor

 

[T Kirtley]

This should work:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"3a","")))/LEN("3a")

HTH,

TK

 

[Dave Peterson]

=(LEN(A1)-LEN(SUBSTITUTE(A1,"3a","")))/LEN("3a")

Hi all,

A certain character or group of characters appears a number of times in the
content of a cell (3a appears two times in d763ah555#3abds3j. With what
formula or code can I count that number? I think I knew it once, but I can't
remember.

Jack Sons
The Netherlands

 

[Marcelo]

Hi Jack
use this code:
**********************************************
Function CountChar(MyChar, Mystring)
Dim counter As Integer

CountChar = 0

For counter = 1 To Len(Mystring)
If Mid(Mystring, counter, 2) = MyChar Then CountChar = CountChar + 1
Next counter

End Function
**********************************************
" If Mid(Mystring, counter, 2) " the 2 on this part of the code said how
many char you are looking for, if you need to count just 3's or a's change it
to 1

use a funcion =countchar("3a",a1)


hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Jack Sons" escreveu:

 

[Yvonne_G]

Trevor, TK and Dave,

Thank you for your answer, but I was looking for a formula without
substitution, which will be more practical (for me). Any ideas?

Jack.

 

[Jack Sons]

Marcelo,

How does the code know in what string I am looking (Mystring?) an what
characters I am looking for (MyChar?)

What lines of code do I need for that? A box popping up in which to tell wat
the adress of Mystring is (what if the string is the result of a formuala in
that cell?) and in which to fill in the characters of MyChar, would be
handy. Can I get the result of the count also in that box?

Thanks in advance.

Jack.

 

[Dave Peterson]

None from me.

Why can't you use =substitute() in the formula?

 

[Trevor Shuttleworth]

Why ? What is it you need to achieve ? Surely, all you need is the answer
to the question ?

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

A1 = d763ah555#3abds3j
B1 = 3a
C1 = 2 (the formula shown above)

Assumes the full string is in cell A1 and what you are looking for is in
cell B1. Still uses SUBSTITUTE though. You don't actually change the
original value if that is your concern.

Do you have some idea of the formula you want and you need help in making it
work ?

Regards

Trevor

 

[Jack Sons]

Trevor, Dave,

"Still uses SUBSTITUTE though. You don't actually change the original value
if that is your concern."

Indeed, that was the point of my question, I now realize that substitute
does not change the the string.

Thanks a lot.

Jack.

 

[Trevor Shuttleworth]

You're welcome. Thanks for the feedback. Glad we got to the solution for
you.