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!)?

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

---
Regards,
Norman


"Vsevolod" wrote in message
m...
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!)?

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

--
Message posted from http://www.ExcelForum.com

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

---
Regards,
Norman

"BrianB " wrote in message
...
Interestingly I have been unable to get a macro to work on a comma
delimited cell without a "Type mismatch" error.

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

Come back with any probs.


---
Message posted from http://www.ExcelForum.com/