Count the strings inside an excel column

Question

Having an excel column as below:

I need to find out the number of words in the column.

What are the odds there is a comma inside the substrings? E.g: ['A','B,C,D','E,F'] — JvdV
– JvdV, Commented Dec 12, 2022 at 12:13

Harun24hr · Accepted Answer · 2022-12-12 09:34:08Z

2

Try below formula-

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

For dynamic spill array.

=MAP(A2:A5,LAMBDA(x,LEN(x)-LEN(SUBSTITUTE(x,",",""))+1))

answered Dec 12, 2022 at 9:27

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dada, TEXTSPLIT() was nice, why did you miss that

@MayukhBhattacharya For older version support as well as, Jos Woolley already posted that.

Harun Dada, this will be short: =MAP(A2:A3,LAMBDA(x,COUNTA(TEXTSPLIT(x,","))))

Alright dada. But you could have shown with MAP()

@MayukhBhattacharya The I would go with =MAP(A2:A5,LAMBDA(x,SUM(--(TEXTSPLIT(x,",")<>"")))).

|

Jos Woolley · Accepted Answer · 2022-12-12 09:24:05Z

1

If you have TEXTSPLIT:

=COUNTA(TEXTSPLIT(A1,","))

answered Dec 12, 2022 at 9:24

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges