View Single Post
  #3   Report Post  
Old January 7th 10, 07:00 PM posted to microsoft.public.excel.misc
T. Valko T. Valko is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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