STRINGS AND SUBSTRINGS !
 

If a cell contains a single string like "VBA is nice to know". How do I
use vba (programmatically)to write the 5 substrings "VBA" "is" "nice"
"to" "know" in five different cells (ie one substring per cell)?
Assume the main string is in cell A2, and the substrings go into cells
C2, D2, E2, F2, and G2 respectively.
Please note that I will **need** a macro.
Any help would be greatly appreciated!

Thanks
Jay Dean


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

STRINGS AND SUBSTRINGS !
 

Hi Jay,

Try:

Sub Tester()
Dim Sstr As String
Dim vArr As Variant
Dim rng As Range
Dim i As Long

Set rng = Range("A2")

Sstr = rng.Value '"VBA is nice to know"

vArr = Split(Sstr)

For i = LBound(vArr) To UBound(vArr)
rng.Offset(0, i + 1).Value = vArr(i)
Next
End Sub


---
Regards,
Norman



"jay dean" wrote in message
...

If a cell contains a single string like "VBA is nice to know". How do I
use vba (programmatically)to write the 5 substrings "VBA" "is" "nice"
"to" "know" in five different cells (ie one substring per cell)?
Assume the main string is in cell A2, and the substrings go into cells
C2, D2, E2, F2, and G2 respectively.
Please note that I will **need** a macro.
Any help would be greatly appreciated!

Thanks
Jay Dean


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

STRINGS AND SUBSTRINGS !
 

Hi Jay,

The Split function was introduced in xl2k. If you are using an earlier
version, post back for a workaround.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Jay,

Try:

Sub Tester()
Dim Sstr As String
Dim vArr As Variant
Dim rng As Range
Dim i As Long

Set rng = Range("A2")

Sstr = rng.Value '"VBA is nice to know"

vArr = Split(Sstr)

For i = LBound(vArr) To UBound(vArr)
rng.Offset(0, i + 1).Value = vArr(i)
Next
End Sub


---
Regards,
Norman

STRINGS AND SUBSTRINGS !
 

Norman-
I am using officeXP, and thanks a boat load! This is excellent!! I love
the fact that the function actually returns an array.

Thanks again.
Jay Dean


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!