Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count specific characters in a cell
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
or, for a non-case-sensitive 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |