Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the number of times a cell value is within a specific range | Excel Worksheet Functions | |||
How do I count the number of times a letter is used in a cell? | Excel Discussion (Misc queries) | |||
count each cell that have a number and take that number and count. | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
how to count the number of decimal places in a cell? | Excel Worksheet Functions |