[Solved] Find and count a character in a cell

normansimonr · Post by **normansimonr** » Thu Jul 14, 2011 4:53 pm

Hi everyone and good wishes for all. I have a question about calc functions. I've got a cell with the entire name of a person: Ex.: "Gabrielle Antoine Dugos Fermin".

And I need to count the number of times that the blankspace " " appears within the character string, say, 4 times. If I have a cell "John Camille Smith" I'd like to see a number 2, of 2 times.

What function is useful for this?

Thank you very much.

Post by **karolus** » Thu Jul 14, 2011 5:14 pm

Hi

Code: Select all

=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))

Karolus

normansimonr · Post by **normansimonr** » Thu Jul 14, 2011 5:21 pm

Great!... it works. Could you help me explaining this a l'e more?

Thanks.

Post by **RoryOF** » Thu Jul 14, 2011 6:03 pm

The SUBSTITUTE function is dealt with at

http://wiki.services.openoffice.org/wik ... E_function
and at
http://www.openofficetips.com/2005/11/1 ... ubstitute/

normansimonr · Post by **normansimonr** » Thu Jul 14, 2011 7:45 pm

Thankyou very much.

NHEngineer · Post by **NHEngineer** » Fri Sep 30, 2011 4:43 pm

karolus wrote:Hi
Code: Select all
=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))
Karolus

Thanks Karolus, it works slick. You saved the day.

David Lee

thnidu · Post by **thnidu** » Wed Jun 25, 2014 4:56 am

Great! I need to count plus signs in a membership activity sheet to know how many people a cell represents, as in

Harry: 1 person
Debra + Pam: 2 people
Joe + Jo + Ĝo: 3 people

I couldn't think of a way to do it. Once I asked the question, this was the first answer to come up and it's perfect.

[Solved] Find and count a character in a cell

[Solved] Find and count a character in a cell

Re: Find and count a character in a cell

Re: Find and count a character in a cell

Re: Find and count a character in a cell

Re: Find and count a character in a cell

Re: Find and count a character in a cell

Re: [Solved] Find and count a character in a cell