# Can I replicate the SPLIT function in Excel 97?

#### sts023

##### Board Regular
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!

#### sts023

##### Board Regular
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
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``````

#### sts023

##### Board Regular
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....