Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count number of pieces data is split to by a certain delimiter

Data looks like this:

Aaa, Bbb, Ccc, Ddd, Eee, Fff
Aaa, Bbb, Ccc, Ddd, Eee
Aaa, Bbb, Ccc, Ddd
Aaa, Bbb, Ccc
Aaa, Bbb
Aaa

Data in rows is split by the ',' delimiter. I want to get the number of parts that each row is split to. Here is the figure:

enter image description here

So, in the 1st row the data is split to 6 parts by ',' delimiter. In the 2nd - 5 parts and so on.

What formula should I use is the column B?

like image 762
Erba Aitbayev Avatar asked Dec 03 '15 03:12

Erba Aitbayev


1 Answers

I'd use this formula:

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

In case you need 0, when the cell is empty (the above formula returns 1), use this:

=IF(A1="",0,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)
like image 195
ZygD Avatar answered Sep 17 '22 15:09

ZygD