Count comma separated items in a string

[NewOrderFac33]

Good morning,

I have just spent hours poring over some data where I could have saved myself a lot of trouble with a simple formula.

I have a range of cells containing labels such as "Live, Pre-Production", "UAT, Pre-Production, Live", "Live, Inactives, UAT, Development" etc.

I needed to count the number of items in each cell, assuming that they were separated by commas. So, the formula would need to return the value 2 for the first of my examples, 3 for the second and 4 for the third.

It needs to use the logic "If the cell is blank, return 0, if the cell is not blank, but does not contain a comma, return 1, otherwise, return (number of commas +1) i.e. if there are two commas in the string, then there must be three items, and so on.

I understand the logic of what I'm trying to do, I'm just stuck on the bit that counts the number of commas found in the string - can anyone help?

As always, all advice gratefully received!

Pete

 

[VoG]

Perhaps

=1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

 

[p45cal]

perhaps:
=IF(LEN(D8)>0,LEN(D8)-LEN(SUBSTITUTE(D8,",",""))+1,0)

 

[NewOrderFac33]

Thank you both - both work fine so long as there is a value in the test cell cell, but, VoG, if the test cell is blank, yours still returns 1.

 

[VoG]

Thank you both - both work fine so long as there is a value in the test cell cell, but, VoG, if the test cell is blank, yours still returns 1.

Try

=IF(A1="",0,1+LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

 

[p45cal]

both work fine so long as there is a value in the test cell cell

I think you'll find mine returns 0 for a blank cell as you requested, however, if that cell is not really blank and has space or spaces in it will return 1. To correct this blank-cell-which-is-not-really-blank problem, you can use trim:
=IF(LEN(TRIM(D8))>0,LEN(D8)-LEN(SUBSTITUTE(D8,",",""))+1,0)

 

[NewOrderFac33]

What splendid people - thank you VERY much for both solutions! Have a good weekend.

 

[Inquirer23]

Hi, may I inquire on a similar matter?

Using the original example, 'I have a range of cells containing labels such as "Live, Pre-Production", "UAT, Pre-Production, Live", "Live, Inactives, UAT, Development",' may I know how can I count the number of instances of each activity in such a way that will show something like this:

Live = 3
Pre-Production = 2
UAT = 2
Inactives = 1
Development = 1

Thank you.

Jong

 

[Aladin Akyurek]

@Inquirer23

Must the output just contain counts or both the activities and their counts?

 

[Clyde76]

=COUNTIF($E$13:$E$16,"*"&H13&"*")

1) Identify your unique values in a column
2) In the adjacent cell, enter the formula =COUNTIF($E$13:$E$16,"*"&H13&"*") where E13:E16 is the range you want to search and H13 is the value you want to find. By inserting H13 between two *, you'll be performing a wildcard search.

Good luck!