| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to count occurence of multiple characters in a cell | Excel Worksheet Functions | |||
| Excel 2002: How to eliminate space within a cell and count items? | Excel Discussion (Misc queries) | |||
| count of tab characters in a single cell | Excel Worksheet Functions | |||
| cHARACTERS BEFORE THE SPACE | Excel Worksheet Functions | |||
| Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Linear Mode
