Wednesday, October 12, 2011

yogi_Find Number Of Items Delimited By A Specified Character

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
a user asked:
How do I find the total number of instances of a character over a range of string cells?
I have a column in which the cells are either empty or contain a string. If it contains a string, it may either have a single item, or multiple items separated by commas. I need to know how many items total are in the column.
Example:
|A, B|
| |
| |
|A|
|B, W, S|
|G|
Should return 7.
I've figured out I can add the number of cells which are not empty(4) to the number of commas across the entire column(3) to get the answer: 4+3=7. Finding the number of cells which are not empty was easy, but I cannot find a way to count the number of commas. The fact that a cell may contain more than one comma is screwing up a previous suggestion I found on these forums. Any ideas?
-------------------------------------
I don't know about he example provided by the user, but I am going to use my hunch that the list of items is separated by the delimiting character