count number of appearences in cell
 

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

count number of appearences in cell
 

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


"Jack Sons" wrote in message
...
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

count number of appearences in cell
 

This should work:

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

HTH,

TK

"Jack Sons" wrote:

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

count number of appearences in cell
 

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

Jack Sons wrote:

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

--

Dave Peterson

count number of appearences in cell
 

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:

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

count number of appearences in cell
 

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" schreef in bericht
...
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

count number of appearences in cell
 

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.


"Marcelo" schreef in bericht
...
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:

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

count number of appearences in cell
 

None from me.

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

Yvonne_G wrote:

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" schreef in bericht
...
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


--

Dave Peterson

count number of appearences in cell
 

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


"Yvonne_G" wrote in message
...
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" schreef in bericht
...
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

count number of appearences in cell
 

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" schreef in bericht
...
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


"Yvonne_G" wrote in message
...
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" schreef in bericht
...
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

count number of appearences in cell
 

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


"Jack Sons" wrote in message
...
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" schreef in bericht
...
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


"Yvonne_G" wrote in message
...
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" schreef in bericht
...
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