Count specific character in a cell in openoffice calc

Question

I have a cell with some text content. For example: "Red, shirt, size," I need to count how many times the comma is used in this cell. The result should be "3"

Any ideas?

score 8 · Accepted Answer · answered Sep 24 '15 at 08:39

You can use the following formula: LEN(Cell)-LEN(SUBSTITUTE(Cell;"YourCharacter";""))

In your case, the formula would be: LEN(Cell)-LEN(SUBSTITUTE(Cell;",";""). LEN(Cell) does the following: Counts the number of characters in your cell. LEN(SUBSTITUTE(Cell;"YourCharacter";"")) counts the number of characters in your cell without the character ",". By subtracting the second formula, you get the number of occurrences of your character.

score 3 · Answer 2 · answered Sep 24 '15 at 08:40

3

You can use LEN() function as below

=LEN(cell)-LEN(SUBSTITUTE(cell;",";""))

answered Sep 24 '15 at 08:40

Zaheer Khorajiya

466
3
11

Count specific character in a cell in openoffice calc

2 Answers2