Looking for formula to count commas in a single cell

Hi jj,

I re-read your original question after SG's heads-up on the reason for the +1 at the end, and noticed this:

"For example cell N2 contains: Books_And_Zines,Bookmark,book,recycled,rare,spoon,kitchen,antique,nonfiction,fi ction,gift,men,women,silverware

Cell O2 contains the formula: =SEARCH(",",N2)+1

I expect it to return 14 because there are 13 commas and +1 to account for the last word. BUT it returns 17. ??"

17 is the correct result for that formula.

SEARCH (and the similar FIND) search a text string for the specified substring (in this case, a comma) and return a number indicating the position in the string of the first occurrence of that substring.

In the example string, "Books_and_Zines" is 15 characters, the first comma is the sixteenth, so SEARCH returns 16, and +1 makes it 17.

SEARCH will take a third argument as well, specifying where in the string to start the search.

SEARCH(",",N2,17)+1 would find the next comma at position 25 and return 26.

Not all that useful in your current task—finding the number of keywords in the list in each cell—but combined with MID, SEARCH or FIND could be useful in extracting the individual keywords from each list.

Regards,

Barry

Barry wrote:

and the +1 at the end creates an incorrect result.

Hi Barry,

The +1 is needed to count the number of comma-separated keywords, e.g.,

key1, key2, key3 has 2 commas but 3 keywords.

SG

Hi SG,

S.A.S.S* must have kicked in again! Read jj's solved post before reading the question, then read only the part about "counting commas" and the formula.

Thanks for the heads-up!

Regards,

Barry

*Short Attention Span Syndrome" Possibly an offshoot of the Evelyn Wood classes way back when.

Hi jj,

Same core formula as you'd use in Numbers, but the TRIM part seems unnecessary, and the +1 at the end creates an incorrect result.

TRIM's usual purpose is to remove extra spaces before, after or in a text string, and I don't see a need to do that in order to count commas.

Core formula: LEN(N2)-LEN(SUBSTITUTE(N2,",","")

Here's an example showing results from both formulas for text strings with and without extra spaces:

A2 and A3 contain two text strings listed below. The strings are identical except for double spaces before orange and blue and a space after the period in A3. (I have used option-space in the text below, as the ASC software automatically trims multiple spaces.)

A2: Bob had red, orange, yellow, green, blue, indigo and violet buttons on his rainbow vest.

A3: Bob had red, orange, yellow, green, blue, indigo and violet buttons on his rainbow vest.

B2: LEN(A2)−LEN(SUBSTITUTE(A2,",",""))

C2: LEN(TRIM(A2))−LEN(SUBSTITUTE(TRIM(A2),",",""))+1

Both formulas are filled down into row 3.

Regards,

Barry