PDA

View Full Version : Solved: count the number of ";" in cell



dani9
08-15-2011, 12:48 AM
Hi,

does anyone know how to count how many times this sign ";" appears in one cell.

I have data like this:
KUSFO1;KUSFO2;KUSFO5;R36Y03;Z2Y00

so in this case it would be 4. But how do i put it in a code? i have like 50.000 rows of this kind of data and I need to split them into rows.

Bob Phillips
08-15-2011, 01:03 AM
Try

=LEN(A2)-LEN(SUBSTITUTE(A2,";",""))

dani9
08-15-2011, 01:12 AM
Try

=LEN(A2)-LEN(SUBSTITUTE(A2,";",""))

That was fast! Thanks a lot!

Bob Phillips
08-15-2011, 01:16 AM
It's an old one, I have used it many times.