On 13 Apr 2007 01:30:00 -0700, "keri" wrote:

On 13 Apr, 00:41, Ron Rosenfeld wrote:
On 12 Apr 2007 12:35:49 -0700, "Craig B" wrote:

Or set up a looping istr search

OR just use the Split function
--ron

Hi,

Thanks for your replies. I tried using text to columns but it appears
I have to specify the length of the array, so this does not work.

I had not heard of the split function but have found this code;

Sub SplitDemo()
Dim txt As String
Dim x As Variant
Dim i As Long
txt = ActiveSheet.Range("a23")
x = Split(txt, " ")
For i = 0 To UBound(x)
Debug.Print x(i)
Next i

What I am unsure how to do is split the text into columns B23 - L23 as
I am unsure how to reference each part of the split text to name a
destination.

Many thanks.

Text to columns has an option which allows you to specify a delimiter which, in
your case, you would specify a <space. Of course, since your source is in A4
and your destination is B5, you might also have to do some copy/paste
operations.

To use the Split function, given your parameters in the OP, try this:

-------------------------------------------------

Option Explicit

Sub ParseString()
Dim rSrc As Range
Dim rDest As Range
Const sDelim As String = " "
Dim Temp As Variant
Dim i As Long

Set rSrc = Range("A4")
Set rDest = Range("B5")

Temp = Split(rSrc.Text, sDelim, -1)

For i = 0 To UBound(Temp)
rDest.Offset(0, i).Value = Temp(i)
Next i

End Sub
==================================================



--ron