Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of spaces...
Hello.
I know someone showed me this before, but I've forgotten. If I wanted to do a count of the number of spaces within a single cell, how could I do that? For instance, if I wanted to look at a cell that contained 'A B C D', and get the answer that there are three spaces in that. Thanks. Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of spaces...
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "mark" wrote in message ... Hello. I know someone showed me this before, but I've forgotten. If I wanted to do a count of the number of spaces within a single cell, how could I do that? For instance, if I wanted to look at a cell that contained 'A B C D', and get the answer that there are three spaces in that. Thanks. Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
number of spaces...
=(len(a1)-len(substitute(a1," ","")))/len(" ")
Since you're only counting a single character, you could eliminate the denominator: =len(a1)-len(substitute(a1," ","")) mark wrote: Hello. I know someone showed me this before, but I've forgotten. If I wanted to do a count of the number of spaces within a single cell, how could I do that? For instance, if I wanted to look at a cell that contained 'A B C D', and get the answer that there are three spaces in that. Thanks. Mark -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling Data off Web - Need Function Help | Excel Worksheet Functions | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
how do I remove empty spaces trailing a text string? | Excel Worksheet Functions |