Count the words in a cell
in [Functions]
|
Prev: Totaling text cells
Next: Data Validation
From: Lars-�ke Aspelin on 20 May 2010 15:38 On Thu, 20 May 2010 11:24:51 -0700 (PDT), Bernd P <bplumhoff(a)gmail.com> wrote: >On 20 Mai, 18:30, "T. Valko" <biffinp...(a)comcast.net> wrote: >> Try this... >> >> =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("O"),"")) >> >> Note that SUBSTITUTE is case sensitive. >> >> -- >> Biff >> Microsoft Excel MVP >> >> "Elton Law" <Elton...(a)discussions.microsoft.com> wrote in message >> >> news:43F1AAB9-8361-454C-821C-78917358F132(a)microsoft.com... >> >> > Dear exper >> > A cell contains a lot of information. >> > Is it possible to count certan word in a cell please? >> >> > Say A1 contains a series of text .... >> >> > 1. Daily Swing even though Sto is hit high or low >> > 2. Look at Chart pattern >> > 3. Stop at dangerous level >> >> > How can I count number of "O" in the cell A1 please? >> >> > Thanks >> > Elton > >Hi Biff, > >I like that UPPER("O") part :-) > >Have fun, >Bernd To be really sure, make it TRIM(LEFT(UPPER("O"))) :-) Lars-�ke
From: T. Valko on 20 May 2010 16:18 >I like that UPPER("O") part So then you know why it's there? -- Biff Microsoft Excel MVP "Bernd P" <bplumhoff(a)gmail.com> wrote in message news:02078050-59f7-411e-ae3f-95053a9df76a(a)l6g2000vbo.googlegroups.com... > On 20 Mai, 18:30, "T. Valko" <biffinp...(a)comcast.net> wrote: >> Try this... >> >> =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("O"),"")) >> >> Note that SUBSTITUTE is case sensitive. >> >> -- >> Biff >> Microsoft Excel MVP >> >> "Elton Law" <Elton...(a)discussions.microsoft.com> wrote in message >> >> news:43F1AAB9-8361-454C-821C-78917358F132(a)microsoft.com... >> >> > Dear exper >> > A cell contains a lot of information. >> > Is it possible to count certan word in a cell please? >> >> > Say A1 contains a series of text .... >> >> > 1. Daily Swing even though Sto is hit high or low >> > 2. Look at Chart pattern >> > 3. Stop at dangerous level >> >> > How can I count number of "O" in the cell A1 please? >> >> > Thanks >> > Elton > > Hi Biff, > > I like that UPPER("O") part :-) > > Have fun, > Bernd
From: Bernd P on 20 May 2010 16:36 On 20 Mai, 20:38, Lars-Åke Aspelin <lar...(a)REMOOVEtelia.com> wrote: > On Thu, 20 May 2010 11:24:51 -0700 (PDT), Bernd P > > > > <bplumh...(a)gmail.com> wrote: > >On 20 Mai, 18:30, "T. Valko" <biffinp...(a)comcast.net> wrote: > >> Try this... > > >> =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("O"),"")) > > >> Note that SUBSTITUTE is case sensitive. > > >> -- > >> Biff > >> Microsoft Excel MVP > > >> "Elton Law" <Elton...(a)discussions.microsoft.com> wrote in message > > >>news:43F1AAB9-8361-454C-821C-78917358F132(a)microsoft.com... > > >> > Dear exper > >> > A cell contains a lot of information. > >> > Is it possible to count certan word in a cell please? > > >> > Say A1 contains a series of text .... > > >> > 1. Daily Swing even though Sto is hit high or low > >> > 2. Look at Chart pattern > >> > 3. Stop at dangerous level > > >> > How can I count number of "O" in the cell A1 please? > > >> > Thanks > >> > Elton > > >Hi Biff, > > >I like that UPPER("O") part :-) > > >Have fun, > >Bernd > > To be really sure, make it TRIM(LEFT(UPPER("O"))) :-) > > Lars-Åke Takk, Lars! :-) Regards, Bernd
From: T. Valko on 20 May 2010 16:54
>To be really sure, make it TRIM(LEFT(UPPER("O"))) To be really super-duper extra sure you might want to use: CLEAN(TRIM(PROPER(LEFT(UPPER(CHAR(79)),1)))) -- Biff Microsoft Excel MVP "Lars-�ke Aspelin" <larske(a)REMOOVEtelia.com> wrote in message news:pq3bv55njl85gr0fci9lplau1h1id90i97(a)4ax.com... > On Thu, 20 May 2010 11:24:51 -0700 (PDT), Bernd P > <bplumhoff(a)gmail.com> wrote: > >>On 20 Mai, 18:30, "T. Valko" <biffinp...(a)comcast.net> wrote: >>> Try this... >>> >>> =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("O"),"")) >>> >>> Note that SUBSTITUTE is case sensitive. >>> >>> -- >>> Biff >>> Microsoft Excel MVP >>> >>> "Elton Law" <Elton...(a)discussions.microsoft.com> wrote in message >>> >>> news:43F1AAB9-8361-454C-821C-78917358F132(a)microsoft.com... >>> >>> > Dear exper >>> > A cell contains a lot of information. >>> > Is it possible to count certan word in a cell please? >>> >>> > Say A1 contains a series of text .... >>> >>> > 1. Daily Swing even though Sto is hit high or low >>> > 2. Look at Chart pattern >>> > 3. Stop at dangerous level >>> >>> > How can I count number of "O" in the cell A1 please? >>> >>> > Thanks >>> > Elton >> >>Hi Biff, >> >>I like that UPPER("O") part :-) >> >>Have fun, >>Bernd > > To be really sure, make it TRIM(LEFT(UPPER("O"))) :-) > > Lars-�ke > |