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"))