counting spaces in a string
 

I apologize for asking again but for some reason, I cannot read the old
messages after a certain time period.
I have a long string that contains words and spaces.
1. Is there an easy way of finding the position of the last space in the
string?
2. Is there an easy way to find all the positions of all the spaces?
3. Can parse be used to parse out the string into an array?

Thanks in advance
xnman

counting spaces in a string
 

1. Is there an easy way of finding the position of the last space in the
string?
Lookup the help on InStrRe
2. Is there an easy way to find all the positions of all the spaces
Lookup the help file on InSt
3. Can parse be used to parse out the string into an array
Look up the help file on Spli

counting spaces in a string
 

I apologize for asking again but for some reason, I cannot read the old
messages after a certain time period.
I have a long string that contains words and spaces.

look the following functions:

1. Is there an easy way of finding the position of the last space in the
string?

intLastSpace = InStrRev(strText, " ")

2. Is there an easy way to find all the positions of all the spaces?
3. Can parse be used to parse out the string into an array?

MyArray = Array(Split(strText, " "))

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

counting spaces in a string
 

Thanks for your help.

Melanie Breden wrote:
I apologize for asking again but for some reason, I cannot read the old
messages after a certain time period.
I have a long string that contains words and spaces.


look the following functions:


1. Is there an easy way of finding the position of the last space in the
string?


intLastSpace = InStrRev(strText, " ")


2. Is there an easy way to find all the positions of all the spaces?
3. Can parse be used to parse out the string into an array?


MyArray = Array(Split(strText, " "))

counting spaces in a string
 

In cell A1 I have This is a test
Also I have these two macros:
No 1 works fine, but No 2 produces an error 13 - Type Mismatch
What am I missing in No 2?

No 1:
Sub TestForLastSpace()
strText = Range("A1").Value
Lastspace = InStrRev(strText, " ")
MsgBox ("The answer is " & Lastspace)
End Sub

No 2:
Sub FindAllSpaces()
strText = Range("A1").Value
MyArray = Array(Split(strText, " "))
MsgBox ("The Answer is " & MyArray)
End Sub

TIA,



"Melanie Breden" wrote in message
...
I apologize for asking again but for some reason, I cannot read the old
messages after a certain time period.
I have a long string that contains words and spaces.

look the following functions:

1. Is there an easy way of finding the position of the last space in the
string?

intLastSpace = InStrRev(strText, " ")

2. Is there an easy way to find all the positions of all the spaces?
3. Can parse be used to parse out the string into an array?

MyArray = Array(Split(strText, " "))

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

counting spaces in a string
 

JMay,

These two sub show what you were tryng to do:

Sub FindAllSpaces()
Dim strText As String
Dim i As Integer
Dim j As Integer
strText = Range("A1").Value
j = 2
For i = 1 To Len(strText) - Len(Replace(strText, " ", ""))
j = InStr(j, strText, " ")
MsgBox "Space " & i & " is at position " & j
j = j + 1
Next i
End Sub

And this shows how to use Split:

Sub FindAllItems()
Dim strText As String
Dim i As Integer
Dim myArray As Variant
strText = Range("A1").Value
myArray = Split(strText, " ")
For i = LBound(myArray) To UBound(myArray)
MsgBox "Part " & i & " is " & myArray(i)
Next i
End Sub

HTH,
Bernie
MS Excel MVP

"JMay" wrote in message
news:i5Lmc.5967$nN6.4640@lakeread06...
In cell A1 I have This is a test
Also I have these two macros:
No 1 works fine, but No 2 produces an error 13 - Type Mismatch
What am I missing in No 2?

No 1:
Sub TestForLastSpace()
strText = Range("A1").Value
Lastspace = InStrRev(strText, " ")
MsgBox ("The answer is " & Lastspace)
End Sub

No 2:
Sub FindAllSpaces()
strText = Range("A1").Value
MyArray = Array(Split(strText, " "))
MsgBox ("The Answer is " & MyArray)
End Sub

TIA,

counting spaces in a string
 

A worksheet function for 1

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"xnman" wrote in message
...
I apologize for asking again but for some reason, I cannot read the old
messages after a certain time period.
I have a long string that contains words and spaces.
1. Is there an easy way of finding the position of the last space in the
string?
2. Is there an easy way to find all the positions of all the spaces?
3. Can parse be used to parse out the string into an array?

Thanks in advance
xnman

counting spaces in a string
 

Hi xnman,
First of all I recognize that you have your solution and
thanked Melanie Breden which also lets everyone else
know that you got your answer.

But you also say you can't read old messages after a period of time.
The meaning is ambiguous but I'll try to answer two possibilities
of your statement.

How old do you mean. Microsoft appears to have about
60 days currently for at least this newsgroup, and it appears
you are connecting directly to the Microsoft newsservers.
You should be able to change your view in Mozilla between
not previously read postings and all postings.

If you are trying to search newsgroups you should check
Google Groups. It takes about 12 hours for a message to
get archived. Some help with searching archives.
http://www.mvps.org/dmcritchie/excel/xlnews.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm