Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
NH NH is offline
external usenet poster
 
Posts: 8
Default 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)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default 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)

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to count occurence of multiple characters in a cell MLK Excel Worksheet Functions 4 March 9th 07 01:51 AM
Excel 2002: How to eliminate space within a cell and count items? Mr. Low Excel Discussion (Misc queries) 6 November 24th 06 02:53 PM
count of tab characters in a single cell mark Excel Worksheet Functions 5 May 18th 06 06:55 PM
cHARACTERS BEFORE THE SPACE T De Villiers Excel Worksheet Functions 3 January 19th 06 02:22 AM
Count Number of Characters in a cell? AHJuncti Excel Discussion (Misc queries) 2 June 16th 05 07:39 PM


All times are GMT +1. The time now is 10:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"