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) |