Can I replicate the SPLIT function in Excel 97?

sts023

Board Regular
Joined
Sep 1, 2008
Messages
102
Sorry if this is a mind-numbingly stupid question, but does anyone have any code to replicate the SPLIT function?
I'm running on Excel 97, which dislikes SPLIT intensely!

Thanks in advance....
 

sts023

Board Regular
Joined
Sep 1, 2008
Messages
102
Richard....

Thanks for the help - I suspect it may even worK! ;)

Although I've been in IT for probably longer than you've existed, I have to admit to having been into VBA for nearly half an hour now, so some of my comments may appear to be somewhat inexperienced!

I think I get the general gist of what's happenning - set up a formula to execute a function, then execute it on the input, then copy the results to the output array.
I'm not sure what the line "Split = aryValues" is doing, unless it's a recursive call?

I presume the code you've kindly offered is invoked by something like
Split(Inputline,"~") in my other (equally plagiarised) VBA, (I'm splitting at the tilda char) and puts it's results in
aryValues(x)

Can you refer me to something which explains (in English to a simpleton) the parameters to Evaluate ? It looks promising/exciting/beyond me....


Steve
 

theozz

Active Member
Joined
Jun 11, 2007
Messages
328
Here Userfunction for 97

Code:
Function Split97(r, Optional u As String = ",")
Dim MidStr     As Variant
Dim AnsStr     As Variant
ReDim MidStr(0 To Len(r))
Dim i As Double, j As Double
Dim GetText, GetPoss
GetText = r
For i = 1 To Len(r)
GetPoss = InStr(GetText, u)
     If GetPoss > 0 Then
          MidStr(j) = Left(GetText, GetPoss - 1)
          GetText = Mid(GetText, GetPoss + 1)
          j = j + 1
     Else
          MidStr(j) = Mid(GetText, GetPoss + 1)
          Exit For
     End If
Next
ReDim AnsStr(j)
For i = 0 To j
     AnsStr(i) = MidStr(i)
Next
Split97 = AnsStr
End Function

Sub test()
Dim t As String
t = "a~b~~c~dd"
Range("a7").Resize(2, 7) = Split97(t, "~")
End Sub
same answer Split
 

sts023

Board Regular
Joined
Sep 1, 2008
Messages
102
The service you guys give freely is awesome!

Not only am I getting answers, I'm learning at the same time!!!

I'll now hibernate for a time, whilst happily crashing Excel with my bumbling attempts to understand, customise, and implement these code offerings....
 

Forum statistics

Threads
1,082,631
Messages
5,366,665
Members
400,910
Latest member
TywithXl

Some videos you may like

This Week's Hot Topics

Top