In Calc, how can I count the number of items in a cell?

How could I count the number of items in a list of texts, like names, in a given cell?

I have a list of cells in which there are list of names. I need a way to count how many names are in each cell. Are there functions that do the job?

Yes, there are such functions. Many. Different. Which one to choose depends on what meaning you mean in the words “count the number”. For example, in the text “Jerome David Salinger never quoted Jerome Klapka Jerome” how many times is the name “Jerome”?

Until a better answer…

If there are single spaces between words, and no spaces at the end, you could count the spaces between words, and add 1 to the result.

Given text in cell B2: =LEN(REGEX(B2;"[^ ]*";"";"g"))+1.

LibreOffice Help on REGEX and LEN.

[^…] Any single occurrence of a character, including Tab, Space and Line Break characters, that is not in the list of characters specified inclusive ranges are permitted. For example “[^a-syz]” matches all characters not in the inclusive range ‘a’ through ‘s’ or the characters ‘y’ and ‘z’. (from the List of Regular Expressions)

Tested with LibreOffice (x86); OS: Windows 6.1.

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark (Answer markCorrect answer mark) to the left of the answer that solves your question.

1 Like