Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces in a string
1. Is there a simple way to count the spaces in a long string?
2. If you have multiple spaces in a string, is there a simple way to determine what position in the string, number 4 space is? Thanks xnman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces in a string
1. =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
2. =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "xnman" wrote in message ... 1. Is there a simple way to count the spaces in a long string? 2. If you have multiple spaces in a string, is there a simple way to determine what position in the string, number 4 space is? Thanks xnman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces in a string
"xnman" wrote...
1. Is there a simple way to count the spaces in a long string? Simplest: n = Len(s) - Len(Application.WorksheetFunction.Substitute(s, " ", "")) 2. If you have multiple spaces in a string, is there a simple way to determine what position in the string, number 4 space is? You could use something like n = InStr(1, Application.WorksheetFunction.Substitute(s, " ", Chr(127), _ inst), Chr(127)) -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces in a string
Bob, Thanks for your quick response. I apologize, but I was asking the
questions in reference to vba. Any suggestions on that for both questions? Bob Phillips wrote: 1. =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) 2. =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spaces in a string
Thanks guys! You're the best.
xnman Harlan Grove wrote: "xnman" wrote... 1. Is there a simple way to count the spaces in a long string? Simplest: n = Len(s) - Len(Application.WorksheetFunction.Substitute(s, " ", "")) 2. If you have multiple spaces in a string, is there a simple way to determine what position in the string, number 4 space is? You could use something like n = InStr(1, Application.WorksheetFunction.Substitute(s, " ", Chr(127), _ inst), Chr(127)) -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Insert spaces in String of Numbers | Excel Worksheet Functions | |||
Removing Spaces from string | Excel Worksheet Functions | |||
Cells and and spaces after a string of text | Excel Worksheet Functions | |||
Count Spaces In A String | Excel Programming |