counting spaces in a string

[xnman]

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

 

[Guest]

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

 

[Melanie Breden]

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)

 

[xnman]

Thanks for your help.

look the following functions:




intLastSpace = InStrRev(strText, " ")




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

 

[JMay]

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,

 

[Bernie Deitrick]

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

 

[Bob Phillips]

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)

 

[David McRitchie]

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