Count Characters with space in a cell
 

Please help...is it a way to count characters with space in a cell?

Examples:
Cell A1: Apple and oranges == 17 characters (with spaces); 15 characters
(with no spaces)
Cell A2: Pineapple == 9 characters (with and with no spaces)

Count Characters with space in a cell
 

=len(a1)
will return the count of all characters in A1

=len(substitute(a1," ","")
will count the number of non-spaces in A1

and
=len(a1)-len(substitute(a1," ",""))
will count the number of spaces in A1.



NH wrote:

Please help...is it a way to count characters with space in a cell?

Examples:
Cell A1: Apple and oranges == 17 characters (with spaces); 15 characters
(with no spaces)
Cell A2: Pineapple == 9 characters (with and with no spaces)

--

Dave Peterson

Count Characters with space in a cell
 

Use the formula =len(a1)

"NH" wrote:

Please help...is it a way to count characters with space in a cell?

Examples:
Cell A1: Apple and oranges == 17 characters (with spaces); 15 characters
(with no spaces)
Cell A2: Pineapple == 9 characters (with and with no spaces)

Count Characters with space in a cell
 

And to count the number of words:

=LEN(A1)+1-LEN(SUBSTITUTE(A1," ",""))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dave Peterson" wrote in message
...
=len(a1)
will return the count of all characters in A1

=len(substitute(a1," ","")
will count the number of non-spaces in A1

and
=len(a1)-len(substitute(a1," ",""))
will count the number of spaces in A1.



NH wrote:

Please help...is it a way to count characters with space in a cell?

Examples:
Cell A1: Apple and oranges == 17 characters (with spaces); 15
characters
(with no spaces)
Cell A2: Pineapple == 9 characters (with and with no spaces)

--

Dave Peterson

Count Characters with space in a cell
 

I'd use:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+(A1<"")

It handles empty cells better.

(and maybe toss in Trim() to make sure extra spaces between words won't cause
errors in the count.)

Ragdyer wrote:

And to count the number of words:

=LEN(A1)+1-LEN(SUBSTITUTE(A1," ",""))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dave Peterson" wrote in message
...
=len(a1)
will return the count of all characters in A1

=len(substitute(a1," ","")
will count the number of non-spaces in A1

and
=len(a1)-len(substitute(a1," ",""))
will count the number of spaces in A1.



NH wrote:

Please help...is it a way to count characters with space in a cell?

Examples:
Cell A1: Apple and oranges == 17 characters (with spaces); 15
characters
(with no spaces)
Cell A2: Pineapple == 9 characters (with and with no spaces)

--

Dave Peterson

--

Dave Peterson

Count Characters with space in a cell
 

I didn't consider "empties", but you're right ... more robust.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
I'd use:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+(A1<"")

It handles empty cells better.

(and maybe toss in Trim() to make sure extra spaces between words won't
cause
errors in the count.)

Ragdyer wrote:

And to count the number of words:

=LEN(A1)+1-LEN(SUBSTITUTE(A1," ",""))
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Dave Peterson" wrote in message
...
=len(a1)
will return the count of all characters in A1

=len(substitute(a1," ","")
will count the number of non-spaces in A1

and
=len(a1)-len(substitute(a1," ",""))
will count the number of spaces in A1.



NH wrote:

Please help...is it a way to count characters with space in a cell?

Examples:
Cell A1: Apple and oranges == 17 characters (with spaces); 15
characters
(with no spaces)
Cell A2: Pineapple == 9 characters (with and with no spaces)

--

Dave Peterson

--

Dave Peterson