Home 
Search 
Today's Posts 
#1




Count specific characters in a cell
Is there a way to count the number of times a specific character occurs in a
specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory 
#2




Count specific characters in a cell
It can be done by splitting the data into separate columns and then using a
countif formula but that may not be feasable in the s/s. If you have a whole column of similar data then it's simple to use the Text to Columns feature. More info would help. I hope someone can come up with a way because that would be helpful to me also. "JGreg7" wrote: Is there a way to count the number of times a specific character occurs in a specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory 
#3




Count specific characters in a cell
Try this...
=LEN(A1)LEN(SUBSTITUTE(A1,"A","")) Note that this is case sensitive. If A1 = aBC123ABC the formula will return 1. Only the upper case "A" is being counted. Likewise, if the formula was: =LEN(A1)LEN(SUBSTITUTE(A1,"a","")) Then in this case only the lower case "a" will be counted. If you want to ignore case then try this version: =LEN(A1)LEN(SUBSTITUTE(UPPER(A1),UPPER("A"),"")) That will count both lower and upper case "a" or "A".  Biff Microsoft Excel MVP "JGreg7" wrote in message ... Is there a way to count the number of times a specific character occurs in a specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory 
#4




Count specific characters in a cell
=LEN(A1)LEN(SUBSTITUTE(A1,"A",""))
or, for a noncasesensitive version: =LEN(A1)LEN(SUBSTITUTE(UPPER(A1),"A",""))  Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Arceedee" wrote: It can be done by splitting the data into separate columns and then using a countif formula but that may not be feasable in the s/s. If you have a whole column of similar data then it's simple to use the Text to Columns feature. More info would help. I hope someone can come up with a way because that would be helpful to me also. "JGreg7" wrote: Is there a way to count the number of times a specific character occurs in a specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory 
#5




Count specific characters in a cell
The text strings I have to deal with are actually fairly long. I have been
converting them to text and importing them into Excel as delimited files using the desired character as the delimiter. I then have to sort and tag the data and then reassenmble the text strings. Although this works, it is very tedious. There must be a better way.  Thank you, John Gregory "Arceedee" wrote: It can be done by splitting the data into separate columns and then using a countif formula but that may not be feasable in the s/s. If you have a whole column of similar data then it's simple to use the Text to Columns feature. More info would help. I hope someone can come up with a way because that would be helpful to me also. "JGreg7" wrote: Is there a way to count the number of times a specific character occurs in a specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory 
#6




Count specific characters in a cell
Luke has the answer. Thanks Luke.
"JGreg7" wrote: The text strings I have to deal with are actually fairly long. I have been converting them to text and importing them into Excel as delimited files using the desired character as the delimiter. I then have to sort and tag the data and then reassenmble the text strings. Although this works, it is very tedious. There must be a better way.  Thank you, John Gregory "Arceedee" wrote: It can be done by splitting the data into separate columns and then using a countif formula but that may not be feasable in the s/s. If you have a whole column of similar data then it's simple to use the Text to Columns feature. More info would help. I hope someone can come up with a way because that would be helpful to me also. "JGreg7" wrote: Is there a way to count the number of times a specific character occurs in a specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory 
#7




Count specific characters in a cell
Thank you all for your help with this  I appreciate it.
 Thank you, John Gregory "T. Valko" wrote: Try this... =LEN(A1)LEN(SUBSTITUTE(A1,"A","")) Note that this is case sensitive. If A1 = aBC123ABC the formula will return 1. Only the upper case "A" is being counted. Likewise, if the formula was: =LEN(A1)LEN(SUBSTITUTE(A1,"a","")) Then in this case only the lower case "a" will be counted. If you want to ignore case then try this version: =LEN(A1)LEN(SUBSTITUTE(UPPER(A1),UPPER("A"),"")) That will count both lower and upper case "a" or "A".  Biff Microsoft Excel MVP "JGreg7" wrote in message ... Is there a way to count the number of times a specific character occurs in a specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory . 
#8




Count specific characters in a cell
You're welcome. Thanks for the feedback!
 Biff Microsoft Excel MVP "JGreg7" wrote in message ... Thank you all for your help with this  I appreciate it.  Thank you, John Gregory "T. Valko" wrote: Try this... =LEN(A1)LEN(SUBSTITUTE(A1,"A","")) Note that this is case sensitive. If A1 = aBC123ABC the formula will return 1. Only the upper case "A" is being counted. Likewise, if the formula was: =LEN(A1)LEN(SUBSTITUTE(A1,"a","")) Then in this case only the lower case "a" will be counted. If you want to ignore case then try this version: =LEN(A1)LEN(SUBSTITUTE(UPPER(A1),UPPER("A"),"")) That will count both lower and upper case "a" or "A".  Biff Microsoft Excel MVP "JGreg7" wrote in message ... Is there a way to count the number of times a specific character occurs in a specific cell? For example, if cell A1 has "ABC123ABC" and I wanted to count the number of occurances that "A" showed up, and wanted the out put to be in B1. In this example the desired result would equal "2".  Thank you, John Gregory . 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how do I set a maximum characters in a specific cell  Excel Discussion (Misc queries)  
how do I set a maximum characters in a specific cell  Excel Discussion (Misc queries)  
Counting specific characters in a cell  Excel Discussion (Misc queries)  
How to remove characters in a cell that precede a specific hyphen  Excel Discussion (Misc queries)  
how do I highlite text within a cell (specific characters)  Excel Discussion (Misc queries) 