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

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

## Count Number of Characters in a Cell

To count the 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 the above formula is it can become very, very long as you try to count more cells. In those cases, you can use the Array formula. Array formulas are a way of defining cells for individual calculations. An array formula is applied in any cell in Calc using the 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 the 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 an empty character and subtract the length of a cell after replacement from the total length to find out the occurrence of a character. For example, the below formula returns two, 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 the array function. The below example counts the number of “e” in the A1:A4 range. It only counted the 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 events including both capital and small letters, you can use the LOWER() function, which converts the character to lowercase before the 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 operations.

#### Looking for something else?

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

Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, 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