Thank you for all the footwork. I will definitely try this.
(I appreciate the turnkey solution.)


"Ron Rosenfeld" wrote:

On Fri, 1 Feb 2008 09:36:02 -0800, ORLANDO V
wrote:

Any ideas on how to do a text to columns on sample data below? The only
thing somewhat static is the fact that each row has seven numbers starting
from the right working left. (where "---" equals blank or zero.)
I appreciate any help given?

iShares S&P 500 Index IVV (6.27) (10.86) (6.27) (2.70) 7.07 11.78 ---
iShares S&P Global 100 Index IOO (8.68) (12.15) (8.68) 0.51 9.03 13.47 ---
iShares MSCI EAFE Index EFA (7.85) (13.82) (7.85) (0.05) 13.78 20.22 ---
iShares Lehman Aggregate Bond AGG 2.31 4.15 2.31 9.59 4.88 --- ---

See if this macro does what you want. It may need some tweaking, as well as a
test to make sure all the components are present.

Since you did not indicate how you wanted to parse the text portion, nor what
things would look like if there is a "blank", I made some assumptions which may
be incorrect.

Place the code below into a regular module. Select the data to be parsed, and
execute the macro.


===================
Option Explicit
Sub ParseSpecial()
Dim c As Range
Dim vData As Variant
Dim lStartNums As Long
Dim i As Long
Dim sTemp As String

For Each c In Selection
sTemp = ""
vData = Split(c.Value)
lStartNums = UBound(vData) - 6
Range(c(1, 2), c(1, 11)).ClearContents
c(1, 2).Value = vData(0)
For i = 1 To UBound(vData) - 8
sTemp = sTemp & " " & vData(i)
Next i
c(1, 3).Value = Trim(sTemp)
c(1, 4).Value = vData(i)
For i = UBound(vData) - 6 To UBound(vData)
c(1, 5 + i - UBound(vData) + 6).Value = vData(i)
Next i
Next c
End Sub
============================
--ron