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