Hey everyone, I use CALC to manage Bill Of Materials for circuit boards. I usually end up with cell contents that look like “R3, R5, R6, R10, R12” and I just want to get a quick quantity of that to put “5” in an adjacent cell.
Thank you for your time
For formula a typical approach is to ask first for the lenght of the string, then substitute all "," with nothing "" and subtract the new length.
=LEN(A1)-LEN( SUBSTITUTE (A1; ","; "") ) +1
The +1 compensates your 4 separators for 5 fields.
Find all character “except comma” (the ^\,), so you can count (LEN), +1.
=LEN(REGEX(A1;"[^\,]";"";"g"))+1
Also can count all “R” in the cell:
=LEN(REGEX(A1;"[^R]";"";"g"))
replace
"[^,]"
?
Think about what REGEX(A1;"[^,]";"";“g”) returns.
as mentionned below, it’s not +1, but
+N( NOT( ISBLANK(A1) )) 
(could also be a discussion on empty items ,,, 
)
This works, except on empty cells where it still returns “1”.
Add to your formula with =IF(ISBLANK(A1);"";...) where ... is your current formula