Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ============================ |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing | Excel Discussion (Misc queries) | |||
Instead of Parsing | Excel Discussion (Misc queries) | |||
Help With Parsing Data | Excel Discussion (Misc queries) | |||
Data parsing question | Excel Worksheet Functions | |||
Parsing Data w/ a Formula (another question) | Excel Worksheet Functions |