I believe this will do what you ask

Sub TextToColumns()
Sep = InputBox("Enter the separator type", "Separator")
If Sep = "" Then Exit Sub
For Each Cell In Selection
WholeLine = CStr(Cell.Value)
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
RowNum = 0
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cell.Offset(RowNum, 0).Value = TempVal
Pos = NextPos + 1
RowNum = RowNum + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
Next
End Sub

Dan E

"peter smith" wrote in message
om...
I'm trying to work out how to extract text strings of varying length
from within a cell. The text instances I need to extract are all
between " ".
I then need to paste each piece of extracted text into cells a2, a3
,a4 etc.
I was planning to use 'text to columns' to spearate out the text but
am limited by the maximum number of columns (256).
Essentially what I need is something to provide the functionality of
'text to rows'.

e.g. In cell a1 I have the follwoing text:
"a","b","dog"

and what I need to do is enter "a" in cell a2, "b" in cell a3 and
"dog" in cell a4. Given that my actual data will covers more than 256
cells I can't just use text to columns, then transpose the data.

Any help greatly appreciated.
Peter