Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to apply parsing rules for strings and list the substrings | Excel Worksheet Functions | |||
sum wrt substrings! | Excel Worksheet Functions | |||
Substrings in Excel? | Excel Discussion (Misc queries) | |||
Searching for Substrings Within Strings | Excel Discussion (Misc queries) | |||
Finding substrings within a string | Excel Programming |