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 --- ---

Do you actually get "---" in the data?

It looks as if for every "proper" number you have a decimal point.
What happens if one of the numbers is an integer? Do you get 4.00 or
4 ?

How many different text items do you get? If you only have a limited
number (as I suspect), then you could set up a table like this:

iShares S&P 500 Index IVV ABC
iShares S&P Global 100 Index IOO ABD
iShares MSCI EAFE Index EFA ABE
iShares Lehman Aggregate Bond AGG ABF
and so on ...

and then you could use Find & Replace within a macro (or SUBSTITUTE in
a formula) to change your text values to some three letter code plus a
space, and then do Data | Text-to-columns on what remains, using
<space as the delimiter.

Then you could convert the 3-letter codes back to text again using
INDEX/MATCH.

Hope this helps.

Pete


On Feb 1, 5:36*pm, 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 --- ---

If this comes from the Internet, you might try a web query to import
it, rather than copy & paste (if that's what you did).

Data | Import External Data | New Web Query

See if that brings it in somewhat better parsed.

Otherwise, if the funds are consistent (i.e. always S&P 500 Index IVV,
S&P Global 100 Index IOO, etc.) then you could create a table with
just that piece of the string, one entry for each account. Easy to
first remove the IShares text, which leaves you with the fund info.

Then use a formula something like this to strip out the fund from the
string:
=RIGHT(C2,LEN(C2)-LEN(A1)+1). Where A1 contains the fund name and C2
is your string (without the IShares text)

This formula would return something like (8.68) (12.15) (8.68) 0.51
9.03 13.47 0.

Then you have 7 numbers separated by spaces. Easy to convert to
columns.

So column 1 would contain the fund name. Columns 2-8 would be your
numbers.

You would need a lookup first to match the fund to the string, but
that's easy.

Does that make sense.

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

The three dashes appear in the data but not the quotes.
For integers, I would get 4.00
There are several hundreds different text items.

I think what you are suggesting is to change the text items to something
that will be uniform and easy to parse, then after the parse, change it back
to what it originally was ? That is really thinking outside the box!
Thanks, I'll try it.
I guess once I get the table set up it should be okay going forward since
the text items rarely change.

Let me know if you have any other ideas on this.
Thanks again.





"Pete_UK" wrote:

Do you actually get "---" in the data?

It looks as if for every "proper" number you have a decimal point.
What happens if one of the numbers is an integer? Do you get 4.00 or
4 ?

How many different text items do you get? If you only have a limited
number (as I suspect), then you could set up a table like this:

iShares S&P 500 Index IVV ABC
iShares S&P Global 100 Index IOO ABD
iShares MSCI EAFE Index EFA ABE
iShares Lehman Aggregate Bond AGG ABF
and so on ...

and then you could use Find & Replace within a macro (or SUBSTITUTE in
a formula) to change your text values to some three letter code plus a
space, and then do Data | Text-to-columns on what remains, using
<space as the delimiter.

Then you could convert the 3-letter codes back to text again using
INDEX/MATCH.

Hope this helps.

Pete


On Feb 1, 5:36 pm, 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 --- ---

The three dashes appear in the data but not the quotes.
For integers, I would get 4.00
There are several hundreds different text items.

I think what you are suggesting is to change the text items to something
that will be uniform and easy to parse, then after the parse, change it back
to what it originally was ? That is really thinking outside the box!
Thanks, I'll try it.
I guess once I get the table set up it should be okay going forward since
the text items rarely change.

Let me know if you have any other ideas on this.
Thanks again.





"Pete_UK" wrote:

Do you actually get "---" in the data?

It looks as if for every "proper" number you have a decimal point.
What happens if one of the numbers is an integer? Do you get 4.00 or
4 ?

How many different text items do you get? If you only have a limited
number (as I suspect), then you could set up a table like this:

iShares S&P 500 Index IVV ABC
iShares S&P Global 100 Index IOO ABD
iShares MSCI EAFE Index EFA ABE
iShares Lehman Aggregate Bond AGG ABF
and so on ...

and then you could use Find & Replace within a macro (or SUBSTITUTE in
a formula) to change your text values to some three letter code plus a
space, and then do Data | Text-to-columns on what remains, using
<space as the delimiter.

Then you could convert the 3-letter codes back to text again using
INDEX/MATCH.

Hope this helps.

Pete


On Feb 1, 5:36 pm, 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 --- ---

Glad to be of help, and of prompting some ideas.

Let us know how you get on.

Pete

On Feb 1, 9:27*pm, ORLANDO V
wrote:
The three dashes appear in the data but not the quotes.
For integers, I would get 4.00
There are several hundreds different text items.

I think what you are suggesting is to change the text items to something
that will be uniform and easy to parse, then after the parse, change it back
to what it originally was ? *That is really thinking outside the box! *
Thanks, I'll try it.
I guess once I get the table set up it should be okay going forward since
the text items rarely change.

Let me know if you have any other ideas on this.
Thanks again. *



"Pete_UK" wrote:
Do you actually get "---" in the data?

It looks as if for every "proper" number you have a decimal point.
What happens if one of the numbers is an integer? Do you get 4.00 or
4 ?

How many different text items do you get? If you only have a limited
number (as I suspect), then you could set up a table like this:

iShares S&P 500 Index IVV * * * * * * * * *ABC
iShares S&P Global 100 Index IOO * * * ABD
iShares MSCI EAFE Index EFA * * * * * *ABE
iShares Lehman Aggregate Bond AGG *ABF
and so on ...

and then you could use Find & Replace within a macro (or SUBSTITUTE in
a formula) to change your text values to some three letter code plus a
space, and then do Data | Text-to-columns on what remains, using
<space as the delimiter.

Then you could convert the 3-letter codes back to text again using
INDEX/MATCH.

Hope this helps.

Pete

On Feb 1, 5:36 pm, 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 --- ---- Hide quoted text -

- Show quoted text -

Makes a lot of sense! I will try it. Thank you!


"HKaplan" wrote:

If this comes from the Internet, you might try a web query to import
it, rather than copy & paste (if that's what you did).

Data | Import External Data | New Web Query

See if that brings it in somewhat better parsed.

Otherwise, if the funds are consistent (i.e. always S&P 500 Index IVV,
S&P Global 100 Index IOO, etc.) then you could create a table with
just that piece of the string, one entry for each account. Easy to
first remove the IShares text, which leaves you with the fund info.

Then use a formula something like this to strip out the fund from the
string:
=RIGHT(C2,LEN(C2)-LEN(A1)+1). Where A1 contains the fund name and C2
is your string (without the IShares text)

This formula would return something like (8.68) (12.15) (8.68) 0.51
9.03 13.47 0.

Then you have 7 numbers separated by spaces. Easy to convert to
columns.

So column 1 would contain the fund name. Columns 2-8 would be your
numbers.

You would need a lookup first to match the fund to the string, but
that's easy.

Does that make sense.

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

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

On Mon, 4 Feb 2008 06:38:01 -0800, ORLANDO V
wrote:

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


You're welcome. Let me know how it works out. It may need some tweaking if any
of my assumptions were incorrect.
--ron

On Mon, 04 Feb 2008 09:42:17 -0500, Ron Rosenfeld
wrote:

On Mon, 4 Feb 2008 06:38:01 -0800, ORLANDO V
wrote:

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


You're welcome. Let me know how it works out. It may need some tweaking if any
of my assumptions were incorrect.
--ron

In particular, if this data came from the internet, it may be necessary to
strip out some extraneous, non-printing characters.
--ron

I tried it today and it worked extremely well. No tweaking necessary!
Thank you very much indeed.


"Ron Rosenfeld" wrote:

On Mon, 04 Feb 2008 09:42:17 -0500, Ron Rosenfeld
wrote:

On Mon, 4 Feb 2008 06:38:01 -0800, ORLANDO V
wrote:

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


You're welcome. Let me know how it works out. It may need some tweaking if any
of my assumptions were incorrect.
--ron

In particular, if this data came from the internet, it may be necessary to
strip out some extraneous, non-printing characters.
--ron

I tried it today and it worked extremely well. No tweaking necessary!
Thank you very much indeed.


"Ron Rosenfeld" wrote:

On Mon, 04 Feb 2008 09:42:17 -0500, Ron Rosenfeld
wrote:

On Mon, 4 Feb 2008 06:38:01 -0800, ORLANDO V
wrote:

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


You're welcome. Let me know how it works out. It may need some tweaking if any
of my assumptions were incorrect.
--ron

In particular, if this data came from the internet, it may be necessary to
strip out some extraneous, non-printing characters.
--ron