Splitting text in cell with Excel 97 VBA macro

[Vsevolod]

Hi!

Can somebody help me in the next problem:

I need to transform (split) a sample text in a cell into N-row table
(or array) where N is equal to number of words in the text. Separators
may be "," or ", ". How can I do that with VBA in Excel 97 (only!)?

 

[Norman Jones]

Hi Vsevolod,

For your purposes (xl97), try a function from Tom Ogilvy:

Function Split97(sStr As Variant, sdelim As String) _
As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") _
& """}")
End Function

 

[BrianB]

Interestingly I have been unable to get a macro to work on a comm
delimited cell without a "Type mismatch" error.

I suggest you record a macro using Data/Text to columns on the cell an
then add code to go through the cells to Trim() the value.

Come back with any probs

 

[Norman Jones]

Hi Brian,

The following works for me:

Sub test()

Dim myArr As Variant
Dim mystr As String
Dim i As Long

mystr = "the, old, brown, fox"

myArr = Split97(Trim(mystr), ",")

For i = LBound(myArr) To UBound(myArr)
Debug.Print myArr(i)
Next
'Or:

Sheets(3).Range("A1").Resize(UBound(myArr), 1) _
= Application.Transpose(myArr)

End Sub