Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number of space in a string
Hi,
I'm trying to find a way to count the number of space in a given string. Is there any function that can do that? Thanks for your help, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number of space in a string
Hi Kathy,
Try this. =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) If you want to know the number of WORDS in the string: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 HTH Regards, Howard wrote in message ps.com... Hi, I'm trying to find a way to count the number of space in a given string. Is there any function that can do that? Thanks for your help, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number of space in a string
=(len(a1)-len(substitute(a1," ","")))/len(" ")
Since you're only counting a single character, you could eliminate the denominator: =len(a1)-len(substitute(a1," ","")) wrote: Hi, I'm trying to find a way to count the number of space in a given string. Is there any function that can do that? Thanks for your help, -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number of space in a string
If you want to know the number of WORDS in the string:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 <space<spacetry<space<spacethis<space<space Safer to trim first: =LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1 Biff "L. Howard Kittle" wrote in message . .. Hi Kathy, Try this. =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) If you want to know the number of WORDS in the string: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 HTH Regards, Howard wrote in message ps.com... Hi, I'm trying to find a way to count the number of space in a given string. Is there any function that can do that? Thanks for your help, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number of space in a string
I forgot to say that I'm in VBA and not in Excel.
Is the Substitute function working in VBA too cause I got an error Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number of space in a string
If you're using xl2k or higher, VBA has Replace. If you're using xl97 or below,
you can use application.substitute. Option Explicit Sub testme() Dim SpaceCtr As Long Dim myStr As String myStr = "asdf qwer qwer" 'myStr = Worksheets("sheet1").Range("a1").Value SpaceCtr = Len(myStr) - Len(Replace(myStr, " ", "")) MsgBox SpaceCtr 'or SpaceCtr = Len(myStr) - Len(Application.Substitute(myStr, " ", "")) MsgBox SpaceCtr End Sub wrote: I forgot to say that I'm in VBA and not in Excel. Is the Substitute function working in VBA too cause I got an error Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Displays the number in text. (One thousand two hundred thirty four | Excel Worksheet Functions | |||
help with excel number string | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
last number array from string | Excel Worksheet Functions |