Tag Archives: libreoffice

Number of semicolon separated words in libreoffice calc

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

The length of the entire A1 field (white space trimmed) minus the length of the A1 field with all the semicolons removed, plus one.

A string in A1 that looks like “A;B;C” will give the number 3.

I had to figure this out and I thought I’d store it here for later retrieval. Maybe someone else will enjoy it as well. The same formula works in Excel too I think.