Counting letters in a cell

My formula is =LEN(A1) and copied down.
Is there a variation of this formula to count just the letters in each cell
without counting the spaces between words ?
Thanks for the reply.
--
Socrates said: I only know, I don''''''''t know nothing.
I say : I don''''''''t even know, I don''''''''t
know nothing.

David
Sat Mar 15 10:36:29 CDT 2008

=LEN(SUBSTITUTE(A1," ",""))
--
David Biddulph

"Learning Excel" <LearningExcel@discussions.microsoft.com> wrote in message
news:C5EBD677-B5A2-4551-A98E-D8E7A3200666@microsoft.com...
> My formula is =LEN(A1) and copied down.
> Is there a variation of this formula to count just the letters in each
> cell
> without counting the spaces between words ?
> Thanks for the reply.
> --
> Socrates said: I only know, I don''''''''t know nothing.
> I say : I don''''''''t even know, I don''''''''t
> know nothing.

LearningExcel
Sat Mar 15 10:50:00 CDT 2008

Thanks a lot David Biddulph.
--
Socrates said: I only know, I don''''''''t know nothing.
I say : I don''''''''t even know, I don''''''''t
know nothing.


"David Biddulph" wrote:

> =LEN(SUBSTITUTE(A1," ",""))
> --
> David Biddulph
>
> "Learning Excel" <LearningExcel@discussions.microsoft.com> wrote in message
> news:C5EBD677-B5A2-4551-A98E-D8E7A3200666@microsoft.com...
> > My formula is =LEN(A1) and copied down.
> > Is there a variation of this formula to count just the letters in each
> > cell
> > without counting the spaces between words ?
> > Thanks for the reply.
> > --
> > Socrates said: I only know, I don''''''''t know nothing.
> > I say : I don''''''''t even know, I don''''''''t
> > know nothing.
>
>
>

LearningExcel
Sat Mar 15 11:12:00 CDT 2008

Do you happen to have a formula to count WORDS in a cell ?
--
Socrates said: I only know, I don''''''''t know nothing.
I say : I don''''''''t even know, I don''''''''t
know nothing.


"Learning Excel" wrote:

> Thanks a lot David Biddulph.
> --
> Socrates said: I only know, I don''''''''t know nothing.
> I say : I don''''''''t even know, I don''''''''t
> know nothing.
>
>
> "David Biddulph" wrote:
>
> > =LEN(SUBSTITUTE(A1," ",""))
> > --
> > David Biddulph
> >
> > "Learning Excel" <LearningExcel@discussions.microsoft.com> wrote in message
> > news:C5EBD677-B5A2-4551-A98E-D8E7A3200666@microsoft.com...
> > > My formula is =LEN(A1) and copied down.
> > > Is there a variation of this formula to count just the letters in each
> > > cell
> > > without counting the spaces between words ?
> > > Thanks for the reply.
> > > --
> > > Socrates said: I only know, I don''''''''t know nothing.
> > > I say : I don''''''''t even know, I don''''''''t
> > > know nothing.
> >
> >
> >

David
Sat Mar 15 11:51:55 CDT 2008

Depends how you want to define words, but if you want to count spaces and
add one (unless the cell is empty), you could try:
=IF(LEN(A1)=0,0,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)
If you had multiple spaces in one place, that would get it confused, of
course.
--
David Biddulph

"Learning Excel" <LearningExcel@discussions.microsoft.com> wrote in message
news:F44FF5FB-C187-490E-A2EA-DE6968580CAC@microsoft.com...
> Do you happen to have a formula to count WORDS in a cell ?

> "Learning Excel" wrote:
>
>> Thanks a lot David Biddulph.

>> "David Biddulph" wrote:
>>
>> > =LEN(SUBSTITUTE(A1," ",""))
>> > --
>> > David Biddulph
>> >
>> > "Learning Excel" <LearningExcel@discussions.microsoft.com> wrote in
>> > message
>> > news:C5EBD677-B5A2-4551-A98E-D8E7A3200666@microsoft.com...
>> > > My formula is =LEN(A1) and copied down.
>> > > Is there a variation of this formula to count just the letters in
>> > > each
>> > > cell
>> > > without counting the spaces between words ?
>> > > Thanks for the reply.
>> > > --
>> > > Socrates said: I only know, I don''''''''t know nothing.
>> > > I say : I don''''''''t even know, I don''''''''t
>> > > know nothing.
>> >
>> >
>> >

Dave
Sat Mar 15 12:44:52 CDT 2008

Another one that will count the number of "words" separated by spaces:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

Learning Excel wrote:
>
> Do you happen to have a formula to count WORDS in a cell ?
> --
> Socrates said: I only know, I don''''''''t know nothing.
> I say : I don''''''''t even know, I don''''''''t
> know nothing.
>
> "Learning Excel" wrote:
>
> > Thanks a lot David Biddulph.
> > --
> > Socrates said: I only know, I don''''''''t know nothing.
> > I say : I don''''''''t even know, I don''''''''t
> > know nothing.
> >
> >
> > "David Biddulph" wrote:
> >
> > > =LEN(SUBSTITUTE(A1," ",""))
> > > --
> > > David Biddulph
> > >
> > > "Learning Excel" <LearningExcel@discussions.microsoft.com> wrote in message
> > > news:C5EBD677-B5A2-4551-A98E-D8E7A3200666@microsoft.com...
> > > > My formula is =LEN(A1) and copied down.
> > > > Is there a variation of this formula to count just the letters in each
> > > > cell
> > > > without counting the spaces between words ?
> > > > Thanks for the reply.
> > > > --
> > > > Socrates said: I only know, I don''''''''t know nothing.
> > > > I say : I don''''''''t even know, I don''''''''t
> > > > know nothing.
> > >
> > >
> > >

--

Dave Peterson

LearningExcel
Sat Mar 15 15:11:00 CDT 2008

Thanks David. Thanks Dave.
Both formulas work for me, appreciate your time.
--
Socrates said: I only know, I don''''''''t know nothing.
I say : I don''''''''t even know, I don''''''''t
know nothing.


"Dave Peterson" wrote:

> Another one that will count the number of "words" separated by spaces:
>
> =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")
>
> Learning Excel wrote:
> >
> > Do you happen to have a formula to count WORDS in a cell ?
> > --
> > Socrates said: I only know, I don''''''''t know nothing.
> > I say : I don''''''''t even know, I don''''''''t
> > know nothing.
> >
> > "Learning Excel" wrote:
> >
> > > Thanks a lot David Biddulph.
> > > --
> > > Socrates said: I only know, I don''''''''t know nothing.
> > > I say : I don''''''''t even know, I don''''''''t
> > > know nothing.
> > >
> > >
> > > "David Biddulph" wrote:
> > >
> > > > =LEN(SUBSTITUTE(A1," ",""))
> > > > --
> > > > David Biddulph
> > > >
> > > > "Learning Excel" <LearningExcel@discussions.microsoft.com> wrote in message
> > > > news:C5EBD677-B5A2-4551-A98E-D8E7A3200666@microsoft.com...
> > > > > My formula is =LEN(A1) and copied down.
> > > > > Is there a variation of this formula to count just the letters in each
> > > > > cell
> > > > > without counting the spaces between words ?
> > > > > Thanks for the reply.
> > > > > --
> > > > > Socrates said: I only know, I don''''''''t know nothing.
> > > > > I say : I don''''''''t even know, I don''''''''t
> > > > > know nothing.
> > > >
> > > >
> > > >
>
> --
>
> Dave Peterson
>