Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 96
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 96
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I set a maximum characters in a specific cell T. Valko Excel Discussion (Misc queries) 0 November 5th 09 08:58 PM
how do I set a maximum characters in a specific cell Mike H Excel Discussion (Misc queries) 0 November 5th 09 08:56 PM
Counting specific characters in a cell johan Excel Discussion (Misc queries) 2 September 18th 09 11:35 AM
How to remove characters in a cell that precede a specific hyphen brantty Excel Discussion (Misc queries) 18 July 25th 08 09:04 AM
how do I highlite text within a cell (specific characters) tim Excel Discussion (Misc queries) 1 May 20th 05 05:23 AM


All times are GMT +1. The time now is 03:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"