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

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

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" <(E-Mail Removed)> wrote in message
news:78A76E7A-4C89-4979-8869-(E-Mail Removed)...
> 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

=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

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

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

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" <(E-Mail Removed)> wrote in message
> news:78A76E7A-4C89-4979-8869-(E-Mail Removed)...
> > 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
>
>
> .
>

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JGreg7" <(E-Mail Removed)> wrote in message
news:A82B25C0-8E2C-4C7F-9C8C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:78A76E7A-4C89-4979-8869-(E-Mail Removed)...
>> > 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
>>
>>
>> .
>>