Page 1 of 1
[Solved] Find and count a character in a cell
Posted: Thu Jul 14, 2011 4:53 pm
by normansimonr
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.
Re: Find and count a character in a cell
Posted: Thu Jul 14, 2011 5:14 pm
by karolus
Hi
Code: Select all
=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))
Karolus
Re: Find and count a character in a cell
Posted: Thu Jul 14, 2011 5:21 pm
by normansimonr
Great!... it works. Could you help me explaining this a l'e more?
Thanks.
Re: Find and count a character in a cell
Posted: Thu Jul 14, 2011 6:03 pm
by RoryOF
Re: Find and count a character in a cell
Posted: Thu Jul 14, 2011 7:45 pm
by normansimonr
Thankyou very much.
Re: Find and count a character in a cell
Posted: Fri Sep 30, 2011 4:43 pm
by NHEngineer
karolus wrote:Hi
Code: Select all
=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))
Karolus
Thanks Karolus, it works slick. You saved the day.
David Lee
Re: [Solved] Find and count a character in a cell
Posted: Wed Jun 25, 2014 4:56 am
by thnidu
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.