count number of appearences in cell

J

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
 
T

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
 
D

Dave Peterson

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

Jack said:
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
 
M

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:
 
Y

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

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

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
 
J

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

Trevor Shuttleworth

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top