FOR RON ROSENFELD: PARSING QUESTION
 

Hi Ron,

You previously helped me parse some data with the formula (see message at
bottom).
I would like to modify the formula to parse something a little different.
Can you help me, perhaps by telling me which variables to change to use the
formula to parse this: Asset name, Dollar Amount, Percent.

OAO Gazprom sponsored ADR $9,637,954 2.166%
Nestle SA (Reg.) $9,097,788 2.045%
Royal Dutch Shell PLC Class A (United Kingdom) $8,022,044 1.803%
AXA SA $7,452,324 1.675%
Nokia Corp. sponsored ADR $7,054,359 1.586%
Vodafone Group PLC sponsored ADR $6,977,795 1.568%
Allianz AG (Reg.) $6,651,450 1.495%
Roche Holding AG (participation certificate) $6,464,113 1.453%
Telefonica SA $6,334,911 1.424%
Satyam Computer Services Ltd. $6,330,213 1.423%
Toyota Motor Corp. sponsored ADR $6,176,495 1.388%
Rio Tinto PLC sponsored ADR $6,135,075 1.379%
Zurich Financial Services AG (Reg.) $6,028,580 1.355%
Mitsubishi Corp. $5,911,118 1.329%
Companhia Vale do Rio Doce (PN-A) sponsored ADR $5,793,632 1.302%
Vivendi $5,767,543 1.296%
British American Tobacco PLC $5,645,984 1.269%



Thank you.


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

FOR RON ROSENFELD: PARSING QUESTION
 

You can use worksheet formulas:

For a string in cell A2:

in B2: =MID(A2,FIND("$",A2),LEN(A2))
in C2: =TRIM(SUBSTITUTE(A2,B2,""))
in D2: =VALUE(LEFT(B2,FIND(" ",B2)-1))
in E2: =VALUE(MID(B2,FIND(" ",B2)+1,LEN(B2)))

and then copy down to match your data. C will contain asset name... and then Format D for currency,
and E for percent.

You can then convert the formulas to values, and then delete columns A and B if you want.

HTH,
Bernie
MS Excel MVP


"ORLANDO V" wrote in message
...

Hi Ron,

You previously helped me parse some data with the formula (see message at
bottom).
I would like to modify the formula to parse something a little different.
Can you help me, perhaps by telling me which variables to change to use the
formula to parse this: Asset name, Dollar Amount, Percent.

OAO Gazprom sponsored ADR $9,637,954 2.166%
Nestle SA (Reg.) $9,097,788 2.045%
Royal Dutch Shell PLC Class A (United Kingdom) $8,022,044 1.803%
AXA SA $7,452,324 1.675%
Nokia Corp. sponsored ADR $7,054,359 1.586%
Vodafone Group PLC sponsored ADR $6,977,795 1.568%
Allianz AG (Reg.) $6,651,450 1.495%
Roche Holding AG (participation certificate) $6,464,113 1.453%
Telefonica SA $6,334,911 1.424%
Satyam Computer Services Ltd. $6,330,213 1.423%
Toyota Motor Corp. sponsored ADR $6,176,495 1.388%
Rio Tinto PLC sponsored ADR $6,135,075 1.379%
Zurich Financial Services AG (Reg.) $6,028,580 1.355%
Mitsubishi Corp. $5,911,118 1.329%
Companhia Vale do Rio Doce (PN-A) sponsored ADR $5,793,632 1.302%
Vivendi $5,767,543 1.296%
British American Tobacco PLC $5,645,984 1.269%



Thank you.


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

FOR RON ROSENFELD: PARSING QUESTION
 

On Mon, 14 Apr 2008 07:14:01 -0700, ORLANDO V
wrote:

Hi Ron,

You previously helped me parse some data with the formula (see message at
bottom).
I would like to modify the formula to parse something a little different.
Can you help me, perhaps by telling me which variables to change to use the
formula to parse this: Asset name, Dollar Amount, Percent.

OAO Gazprom sponsored ADR $9,637,954 2.166%
Nestle SA (Reg.) $9,097,788 2.045%
Royal Dutch Shell PLC Class A (United Kingdom) $8,022,044 1.803%
AXA SA $7,452,324 1.675%
Nokia Corp. sponsored ADR $7,054,359 1.586%
Vodafone Group PLC sponsored ADR $6,977,795 1.568%
Allianz AG (Reg.) $6,651,450 1.495%
Roche Holding AG (participation certificate) $6,464,113 1.453%
Telefonica SA $6,334,911 1.424%
Satyam Computer Services Ltd. $6,330,213 1.423%
Toyota Motor Corp. sponsored ADR $6,176,495 1.388%
Rio Tinto PLC sponsored ADR $6,135,075 1.379%
Zurich Financial Services AG (Reg.) $6,028,580 1.355%
Mitsubishi Corp. $5,911,118 1.329%
Companhia Vale do Rio Doce (PN-A) sponsored ADR $5,793,632 1.302%
Vivendi $5,767,543 1.296%
British American Tobacco PLC $5,645,984 1.269%



Thank you.

Assuming your data is formatted as:

Asset Name<spaceDollar Amount<spacePercent

Then the following will parse Selection into adjacent columns:

==============================
Option Explicit
Sub ParseData()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(.*)\s+(\S+)\s+(\S+)\s*$"
For Each c In Selection
With c
Range(.Offset(0, 1), .Offset(0, 3)).ClearContents
If re.test(.Value) = True Then
Set mc = re.Execute(.Value)
For i = 0 To 2
.Offset(0, i + 1).Value = Trim(mc(0).submatches(i))
Next i
End If
End With
Next c
End Sub
============================================

You could use formulas, also, or you could rewrite this as a User Defined
Function. But this should give you the general idea.
--ron

FOR RON ROSENFELD: PARSING QUESTION
 

Thank you. I will try it.


"Bernie Deitrick" wrote:

You can use worksheet formulas:

For a string in cell A2:

in B2: =MID(A2,FIND("$",A2),LEN(A2))
in C2: =TRIM(SUBSTITUTE(A2,B2,""))
in D2: =VALUE(LEFT(B2,FIND(" ",B2)-1))
in E2: =VALUE(MID(B2,FIND(" ",B2)+1,LEN(B2)))

and then copy down to match your data. C will contain asset name... and then Format D for currency,
and E for percent.

You can then convert the formulas to values, and then delete columns A and B if you want.

HTH,
Bernie
MS Excel MVP


"ORLANDO V" wrote in message
...

Hi Ron,

You previously helped me parse some data with the formula (see message at
bottom).
I would like to modify the formula to parse something a little different.
Can you help me, perhaps by telling me which variables to change to use the
formula to parse this: Asset name, Dollar Amount, Percent.

OAO Gazprom sponsored ADR $9,637,954 2.166%
Nestle SA (Reg.) $9,097,788 2.045%
Royal Dutch Shell PLC Class A (United Kingdom) $8,022,044 1.803%
AXA SA $7,452,324 1.675%
Nokia Corp. sponsored ADR $7,054,359 1.586%
Vodafone Group PLC sponsored ADR $6,977,795 1.568%
Allianz AG (Reg.) $6,651,450 1.495%
Roche Holding AG (participation certificate) $6,464,113 1.453%
Telefonica SA $6,334,911 1.424%
Satyam Computer Services Ltd. $6,330,213 1.423%
Toyota Motor Corp. sponsored ADR $6,176,495 1.388%
Rio Tinto PLC sponsored ADR $6,135,075 1.379%
Zurich Financial Services AG (Reg.) $6,028,580 1.355%
Mitsubishi Corp. $5,911,118 1.329%
Companhia Vale do Rio Doce (PN-A) sponsored ADR $5,793,632 1.302%
Vivendi $5,767,543 1.296%
British American Tobacco PLC $5,645,984 1.269%



Thank you.


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

FOR RON ROSENFELD: PARSING QUESTION
 

Thank you very much.


"Ron Rosenfeld" wrote:

On Mon, 14 Apr 2008 07:14:01 -0700, ORLANDO V
wrote:

Hi Ron,

You previously helped me parse some data with the formula (see message at
bottom).
I would like to modify the formula to parse something a little different.
Can you help me, perhaps by telling me which variables to change to use the
formula to parse this: Asset name, Dollar Amount, Percent.

OAO Gazprom sponsored ADR $9,637,954 2.166%
Nestle SA (Reg.) $9,097,788 2.045%
Royal Dutch Shell PLC Class A (United Kingdom) $8,022,044 1.803%
AXA SA $7,452,324 1.675%
Nokia Corp. sponsored ADR $7,054,359 1.586%
Vodafone Group PLC sponsored ADR $6,977,795 1.568%
Allianz AG (Reg.) $6,651,450 1.495%
Roche Holding AG (participation certificate) $6,464,113 1.453%
Telefonica SA $6,334,911 1.424%
Satyam Computer Services Ltd. $6,330,213 1.423%
Toyota Motor Corp. sponsored ADR $6,176,495 1.388%
Rio Tinto PLC sponsored ADR $6,135,075 1.379%
Zurich Financial Services AG (Reg.) $6,028,580 1.355%
Mitsubishi Corp. $5,911,118 1.329%
Companhia Vale do Rio Doce (PN-A) sponsored ADR $5,793,632 1.302%
Vivendi $5,767,543 1.296%
British American Tobacco PLC $5,645,984 1.269%



Thank you.

Assuming your data is formatted as:

Asset Name<spaceDollar Amount<spacePercent

Then the following will parse Selection into adjacent columns:

==============================
Option Explicit
Sub ParseData()
Dim c As Range
Dim i As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(.*)\s+(\S+)\s+(\S+)\s*$"
For Each c In Selection
With c
Range(.Offset(0, 1), .Offset(0, 3)).ClearContents
If re.test(.Value) = True Then
Set mc = re.Execute(.Value)
For i = 0 To 2
.Offset(0, i + 1).Value = Trim(mc(0).submatches(i))
Next i
End If
End With
Next c
End Sub
============================================

You could use formulas, also, or you could rewrite this as a User Defined
Function. But this should give you the general idea.
--ron