|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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" <j.sons[ at ]planet.nl> wrote in message
news:eTj8ZC04GHA.292[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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
>
|
|
This should work:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"3a","")))/LEN("3a")
HTH,
TK
"Jack Sons" wrote:
[Quoted Text] > 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
>
>
>
|
|
=(LEN(A1)-LEN(SUBSTITUTE(A1,"3a","")))/LEN("3a")
Jack Sons wrote:
[Quoted Text] >
> 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
|
|
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:
[Quoted Text] > 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, 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" <j.sons[ at ]planet.nl> schreef in bericht
news:eTj8ZC04GHA.292[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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,
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" <Marcelo[ at ]discussions.microsoft.com> schreef in bericht
news:9A9A584A-7A45-4833-9CF6-DDE3579BBEE6[ at ]microsoft.com...
[Quoted Text] > 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
>>
>>
>>
|
|
None from me.
Why can't you use =substitute() in the formula?
Yvonne_G wrote:
[Quoted Text] >
> 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" <j.sons[ at ]planet.nl> schreef in bericht
> news:eTj8ZC04GHA.292[ at ]TK2MSFTNGP02.phx.gbl...
> > 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
|
|
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" <Yvonne_G[ at ]planet.nl> wrote in message
news:%23i3QMaJ5GHA.4256[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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" <j.sons[ at ]planet.nl> schreef in bericht
> news:eTj8ZC04GHA.292[ at ]TK2MSFTNGP02.phx.gbl...
>> 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
>>
>
>
|
|
|