How do you separate numbers and words of varying lengths into columns in Excel 2003?

K

kytrangho

New Member
Joined
Sep 16, 2013
Messages
2
How do I separate the Fund Name, Ticker and Net Flows in this list, which are currently in one column, into three separate columns with Excel 2003? I got it from this site, ETF Fund Flows Tool - IndexUniverse.com, and wanted to reproduce it for a newspaper publication.

I couldn't find an answer to this because the fund names have varying number of words while the tickers have three or four letters and the fund flows have five or six numbers, commas and no commas.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Flows for 09/09/2013 - 09/13/2013

Top 10 Creations

Fund Name
Ticker
Net Flows
Details​

SPDR S&P 500
SPY
4,764.58

​

iShares MSCI Emerging Markets
EEM
2,673.52

​

iShares Russell 2000
IWM
910.17

​

PowerShares QQQ
QQQ
600.83

​

Market Vectors Gold Miners
GDX
559.23

​

Industrial Select SPDR
XLI
521.68

​

iShares MSCI Brazil Capped
EWZ
484.01

​

iShares Core S&P 500
IVV
408.82

​

Vanguard FTSE Emerging Markets
VWO
384.91

​

iShares Floating Rate Bond
FLOT
348.94


​


[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Maybe like this.

Please reply.
Excel Workbook
ABCD
2Fund Name
3Ticker
4Net Flows
5Details
6SPDR S&P 500SPDR S&P 500SPY4,764.58
7SPYiShares MSCI Emerging MarketsEEM2,673.52
84,764.58iShares Russell 2000IWM910.17
9iShares MSCI Emerging MarketsPowerShares QQQQQQ600.83
10EEMMarket Vectors Gold MinersGDX559.23
112,673.52Industrial Select SPDRXLI521.68
12iShares Russell 2000iShares MSCI Brazil CappedEWZ484.01
13IWMiShares Core S&P 500IVV408.82
14910.17
15PowerShares QQQ
16QQQ
17600.83
18Market Vectors Gold Miners
19GDX
20559.23
21Industrial Select SPDR
22XLI
23521.68
24iShares MSCI Brazil Capped
25EWZ
26484.01
27iShares Core S&P 500
28IVV
29408.82
Blad13
Cell Formulas
RangeFormula
B6=A6
B7=INDIRECT("A"&6+((ROW()-6)*3))
B8=INDIRECT("A"&6+((ROW()-6)*3))
B9=INDIRECT("A"&6+((ROW()-6)*3))
B10=INDIRECT("A"&6+((ROW()-6)*3))
B11=INDIRECT("A"&6+((ROW()-6)*3))
B12=INDIRECT("A"&6+((ROW()-6)*3))
B13=INDIRECT("A"&6+((ROW()-6)*3))
C6=A7
C7=INDIRECT("A"&7+((ROW()-6)*3))
C8=INDIRECT("A"&7+((ROW()-6)*3))
C9=INDIRECT("A"&7+((ROW()-6)*3))
C10=INDIRECT("A"&7+((ROW()-6)*3))
C11=INDIRECT("A"&7+((ROW()-6)*3))
C12=INDIRECT("A"&7+((ROW()-6)*3))
C13=INDIRECT("A"&7+((ROW()-6)*3))
D6=A8
D7=INDIRECT("A"&8+((ROW()-6)*3))
D8=INDIRECT("A"&8+((ROW()-6)*3))
D9=INDIRECT("A"&8+((ROW()-6)*3))
D10=INDIRECT("A"&8+((ROW()-6)*3))
D11=INDIRECT("A"&8+((ROW()-6)*3))
D12=INDIRECT("A"&8+((ROW()-6)*3))
D13=INDIRECT("A"&8+((ROW()-6)*3))
 
Upvote 0
Oeldere said:
Maybe like this.

Please reply.

Blad13[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
</thead><tbody>[TR]
[TH]2[/TH]
[TD]Fund Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Ticker[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Net Flows[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Details[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]SPDR S&P 500[/TD]
[TD="bgcolor: #FFFF00"]SPDR S&P 500[/TD]
[TD="bgcolor: #FFFF00"]SPY [/TD]
[TD="bgcolor: #FFFF00, align: right"]4,764.58[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]SPY [/TD]
[TD="bgcolor: #92D050"]iShares MSCI Emerging Markets[/TD]
[TD="bgcolor: #92D050"]EEM [/TD]
[TD="bgcolor: #92D050, align: right"]2,673.52[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]4,764.58[/TD]
[TD="bgcolor: #92D050"]iShares Russell 2000[/TD]
[TD="bgcolor: #92D050"]IWM [/TD]
[TD="bgcolor: #92D050, align: right"]910.17[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]iShares MSCI Emerging Markets[/TD]
[TD="bgcolor: #92D050"]PowerShares QQQ[/TD]
[TD="bgcolor: #92D050"]QQQ [/TD]
[TD="bgcolor: #92D050, align: right"]600.83[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]EEM [/TD]
[TD="bgcolor: #92D050"]Market Vectors Gold Miners[/TD]
[TD="bgcolor: #92D050"]GDX [/TD]
[TD="bgcolor: #92D050, align: right"]559.23[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"]2,673.52[/TD]
[TD="bgcolor: #92D050"]Industrial Select SPDR[/TD]
[TD="bgcolor: #92D050"]XLI [/TD]
[TD="bgcolor: #92D050, align: right"]521.68[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]iShares Russell 2000[/TD]
[TD="bgcolor: #92D050"]iShares MSCI Brazil Capped[/TD]
[TD="bgcolor: #92D050"]EWZ [/TD]
[TD="bgcolor: #92D050, align: right"]484.01[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]IWM [/TD]
[TD="bgcolor: #92D050"]iShares Core S&P 500[/TD]
[TD="bgcolor: #92D050"]IVV [/TD]
[TD="bgcolor: #92D050, align: right"]408.82[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD="align: right"]910.17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]PowerShares QQQ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD]QQQ [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD="align: right"]600.83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD]Market Vectors Gold Miners[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]19[/TH]
[TD]GDX [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]20[/TH]
[TD="align: right"]559.23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]21[/TH]
[TD]Industrial Select SPDR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]22[/TH]
[TD]XLI [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]23[/TH]
[TD="align: right"]521.68[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]24[/TH]
[TD]iShares MSCI Brazil Capped[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]25[/TH]
[TD]EWZ [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]26[/TH]
[TD="align: right"]484.01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]27[/TH]
[TD]iShares Core S&P 500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]28[/TH]
[TD]IVV [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]29[/TH]
[TD="align: right"]408.82[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


CellFormula
B6=A6
C6=A7
D6=A8
B7=INDIRECT("A"&6+((ROW()-6)*3))
C7=INDIRECT("A"&7+((ROW()-6)*3))
D7=INDIRECT("A"&8+((ROW()-6)*3))
B8=INDIRECT("A"&6+((ROW()-6)*3))
C8=INDIRECT("A"&7+((ROW()-6)*3))
D8=INDIRECT("A"&8+((ROW()-6)*3))
B9=INDIRECT("A"&6+((ROW()-6)*3))
C9=INDIRECT("A"&7+((ROW()-6)*3))
D9=INDIRECT("A"&8+((ROW()-6)*3))
B10=INDIRECT("A"&6+((ROW()-6)*3))
C10=INDIRECT("A"&7+((ROW()-6)*3))
D10=INDIRECT("A"&8+((ROW()-6)*3))
B11=INDIRECT("A"&6+((ROW()-6)*3))
C11=INDIRECT("A"&7+((ROW()-6)*3))
D11=INDIRECT("A"&8+((ROW()-6)*3))
B12=INDIRECT("A"&6+((ROW()-6)*3))
C12=INDIRECT("A"&7+((ROW()-6)*3))
D12=INDIRECT("A"&8+((ROW()-6)*3))
B13=INDIRECT("A"&6+((ROW()-6)*3))
C13=INDIRECT("A"&7+((ROW()-6)*3))
D13=INDIRECT("A"&8+((ROW()-6)*3))

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Wow. That's the result I wanted. Thank you very much! What is the logic or rationale behind that coding?
 
Upvote 0
b7 =INDIRECT("A"&6+((ROW()-6)*3))

start in cel 6.

the result in the formula row() in cel b7 =7

(7 - 6) = 1

1 * 3 = 3

6+3 =9

A+9 = cell A9

indirect takes the value from cel A9.

Since the next rows are always 3 rows below the previous one, we use 3 in the multiplier.

Hope I explained well enough.

Please reply.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top