Can I replicate the SPLIT function in Excel 97?

[sts023]

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....

 

[Richard Schollar]

Hi

Check out this post:

http://www.mrexcel.com/forum/showpost.php?p=1176798&postcount=23

I think you may need to comment out:

    If Delimiter = vbNullChar Then 
        Delimiter = Chr(7) 
        Text = Replace(Text, vbNullChar, Delimiter) 
    End If

to get it to work.

 

[sts023]

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]

Here Userfunction for 97

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]

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....