I know I can do this with text-to-columns, but I may have more than 256
strings in my text-file seperated by full stops, and I can't always use
2007.

What I need is a kind of 'Text-to-Rows' feature, so that it puts each string
into the next cell down

Is there an easy way to do this?

M

One way:

Public Sub FullStopDelmitedTextToRows()
Const csDelim As String = "."
Dim vArr As Variant
Dim rCell As Range
With Selection
If .Rows.Count 1 Then
MsgBox "You must choose cells in one row only"
Else
For Each rCell In .Cells
With rCell
If Not IsEmpty(.Value) Then
vArr = Split(.Text, csDelim)
If IsArray(vArr) Then
With .Resize(UBound(vArr) - LBound(vArr) + 1, 1)
If Application.CountA(.Cells) 1 Then
MsgBox "Can only expand into empty cells"
Exit Sub
End If
.Value = Application.Transpose(vArr)
End With
Else
.Value = vArr
End If
End If
End With
Next rCell
End If
End With
End Sub

Note: Error checking is very minimal




In article ,
"Michelle" wrote:

I know I can do this with text-to-columns, but I may have more than 256
strings in my text-file seperated by full stops, and I can't always use
2007.

What I need is a kind of 'Text-to-Rows' feature, so that it puts each string
into the next cell down

Is there an easy way to do this?

M

On Thu, 15 Jan 2009 16:10:09 -0000, "Michelle"
wrote:

I know I can do this with text-to-columns, but I may have more than 256
strings in my text-file seperated by full stops, and I can't always use
2007.

What I need is a kind of 'Text-to-Rows' feature, so that it puts each string
into the next cell down

Is there an easy way to do this?

M

This does literally what you request:

=================================
Option Explicit
Sub TextToRows()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^.]+"

For Each c In Selection 'or however you set up
'the range to process

If re.test(c.Value) = True Then
i = 2
Set mc = re.Execute(c.Value)
For Each m In mc
c(i, 1).Value = m.Value
i = i + 1
Next m
End If
Next c
End Sub
==================================

This is a little cleaner as it strips off the leading <space's that may be
between the "." and the next word:

================================
Option Explicit
Sub TextToRows()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[^.]+"

For Each c In Selection 'or however you set up
'the range to process

If re.test(c.Value) = True Then
i = 2
Set mc = re.Execute(c.Value)
For Each m In mc
c(i, 1).Value = Trim(m.Value)
i = i + 1
Next m
End If
Next c
End Sub
===============================
--ron