Count Characters in LibreOffice Calc Cell

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.

Character Count Using LEN
Character Count Using LEN

 

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.

Count total characters in a Range
Count total characters in a Range

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 Characters using Array Formula
Count Characters using 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 Char Occurrence
Count Char Occurrence

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 occurrence in a range
Count occurrence in a range

 

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.

Count all LOWER+UPPER case characters in a range
Count all LOWER+UPPER case characters in a range

 

Summary

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

If you have any questions, drop a comment below. Don’t forget to follow us in Facebook, Google+ and subscribe.

 

Leave a Reply

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