Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Kelli
 
Posts: n/a
Default Count occurrence of character within a cell

I would like to count the number of a particular character within a cell. In
the cell are store numbers/names separated by the | character. The store
number/name represents a stop that the truck made. There is always one more
| character than stops. An example is: PER | R7 | PER is one stop at R7.
Another example is: BAK | 80 | 42 | BAK is 2 stops- one at 80 and one at 42.
Yes there is a space before and after the | character and PER or BAK is where
the truck started and ended. Thank you for any help.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bernard Liengme
 
Posts: n/a
Default Count occurrence of character within a cell

Not sure from message what to count.
This =LEN(A2)-LEN(SUBSTITUTE(A2,"|","")) counts number of | charters in A2's
value
So =LEN(A2)-LEN(SUBSTITUTE(A2,"|","")) -1 counts stops?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Kelli" wrote in message
...
I would like to count the number of a particular character within a cell.
In
the cell are store numbers/names separated by the | character. The store
number/name represents a stop that the truck made. There is always one
more
| character than stops. An example is: PER | R7 | PER is one stop at R7.
Another example is: BAK | 80 | 42 | BAK is 2 stops- one at 80 and one at
42.
Yes there is a space before and after the | character and PER or BAK is
where
the truck started and ended. Thank you for any help.



  #3   Report Post  
Posted to microsoft.public.excel.misc
Kelli
 
Posts: n/a
Default Count occurrence of character within a cell

Bernard,
Thank you. It works and "|" is the correct character.
Kelli
"Bernard Liengme" wrote:

Not sure from message what to count.
This =LEN(A2)-LEN(SUBSTITUTE(A2,"|","")) counts number of | charters in A2's
value
So =LEN(A2)-LEN(SUBSTITUTE(A2,"|","")) -1 counts stops?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Kelli" wrote in message
...
I would like to count the number of a particular character within a cell.
In
the cell are store numbers/names separated by the | character. The store
number/name represents a stop that the truck made. There is always one
more
| character than stops. An example is: PER | R7 | PER is one stop at R7.
Another example is: BAK | 80 | 42 | BAK is 2 stops- one at 80 and one at
42.
Yes there is a space before and after the | character and PER or BAK is
where
the truck started and ended. Thank you for any help.




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
Count on cell for each time it is changed Jelinek Excel Discussion (Misc queries) 3 January 9th 06 03:22 PM
count cell if value present in every other cell + criteria lrbest4x4xfar Excel Worksheet Functions 2 September 26th 05 01:30 AM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 01:19 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"