This beginner’s tutorial explains different ways of counting characters in LibreOffice Calc.

Counting characters in a cell, sheet, range of cells is often useful when doing certain activities. Counting characters very simple in Calc using various functions. Here’s how.

## Count Number of Characters in a Cell

To count number of characters in a Cell, use LEN() function. This function returns the length of a string inside a cell including spaces.

## Count Number of Characters in a Range of Cells

You can count the characters in a range of cells in total using SUM() and LEN() function together.

However, the problem with above formula is it can become very very long as you try to count more cells. In those cases you can use Array formula. Array formula are a way of defining cells for individual calculations. Array formula is applied in any cell in Calc using CTRL+SHIFT+ENTER key combination (see below). Once you apply said combination, Calc adds curly braces {} to the array formula.

## Count Occurrences of a specific character in a Cell

You can combine LEN() function and SUBSTITUTE() function in Calc to count the occurrence of a specific character in a cell. SUBSTITUTE() function takes three arguments –

SUBSTITUTE(target string, search char, replaced by char)

You can replace any character with empty character and subtract length of cell after replacement from total length to find out the occurrence of a character. For example, below formula returns 2 which counts “e” in cell A3 which contains “Main Street”.

## Count Occurrences of a specific character in a range of cells

Using the previous method, you can count the occurrence of a single character in a range of cells using array function. Below example counts number of “e” in A1:A4 range. Note that it only counted small letter “e” and skipped the capital letter “E”.

## Count Upper and Lower Case occurrence of a specific character

If you want to count all the occurrence including both capital and small letters, you can use LOWER() function which converts the character to lowercase before count. Same above example below gives result 5.

## Summary

This is how you can count characters in LibreOffice Calc cells using functions and combine them to perform more complex operation.

#### Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw. ## Related

### arindam

Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials and no BS. Follow us using social channels or send us an email.

Subscribe
Notify of This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks Hello this was very helpful, but when i am trying to count the occurrence of character in range of cells it is giving me an !VALUE error. Have the same problem, formulas doesn’t work in range of cells After putting the range – press CTRL+SHIFT+ENTER. This would make it as array formula. That means, the formula would have { } curly braces . Check the above example… Hi, thanks for reply. I was using curly braces and I had “#NAME!” error. Everything got fixed when i removed 1-2 normal braces ) and then hit CTRL+SHIFT+ENTER. Libre automatically placed missed ) and { } then it worked. After putting the range – press CTRL+SHIFT+ENTER. This would make it as array formula. That means, the formula would have { } curly braces . Check the above example.

5
0