Simulates the Split function that is not available in Excel 97.

Function Split97(ByVal sExpr As String, _

Optional sDelim As String = ” “, _

Optional lLimit As Long = -1, _

Optional cComp As VbCompareMethod = vbBinaryCompare) As Variant

Dim aTemp() As String

Dim lCount As Long

Dim lLimitCnt As Long

Dim sExprTemp As String

Dim lDelimFnd As Long

lCount = 0

lLimitCnt = lLimit

sExprTemp = sExpr

lDelimFnd = InStr(1, sExprTemp, sDelim, cComp)

Do While CBool(lLimitCnt – 1) And lDelimFnd > 0

ReDim Preserve aTemp(0 To lCount)

aTemp(lCount) = Left(sExprTemp, lDelimFnd – 1)

sExprTemp = Right(sExprTemp, Len(sExprTemp) – (Len(aTemp(lCount)) + 1) _

– (Len(sDelim) – 1))

lCount = lCount + 1

lLimitCnt = lLimitCnt – 1

lDelimFnd = InStr(1, sExprTemp, sDelim, cComp)

Loop

ReDim Preserve aTemp(0 To lCount)

aTemp(lCount) = sExprTemp

Split97 = aTemp

End Function

What’s your point?

The point is that Excel 2000+ has some built-in VBA functions that do not exist in Excel 97. Therefore, users of Excel 97 can use functions such as these to simulate functions that are built in to later versions.

Thanks, good point.

That wasn’t very neighborly of me. I’ve added a short comment at the top. Thanks for pointing this out.

Thanks for this function! This will help lot of programmers (like me) tired enough to do impotant functions (like the one you did).

Thanks again and more power!

Thanks a million

GREAT WORK on this and the Replace function.

Two quick things: First, the VbCompareMethod needs to be changed to Long (like with Replace97). Second, the double quotes need to be just straight double quotes, not angled ones. After I made these quick changes it complied and worked perfectly in Excel 97.

Thanks again, these two functions have been REALLY HELPFUL. ;)

GOOD JOB man

your code has done me well..

Nice job with this. One note that might help others, The function call returned an error with me: “Variable uses Automation type not supported in VB”

To resolve this, take out the VbBinaryCompare. The function call then would read:

Function Split97(ByVal sExpr As String, _

Optional sDelim As String = ” “, _

Optional lLimit As Long = -1) As Variant

Which then operates like the split function in later versions. Thanks again for sharing this.