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

[kytrangho]

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.

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 ​

<tbody>
</tbody>

Any help would be greatly appreciated!

 

[Oeldere]

Maybe like this.

Please reply.

Excel Workbook
	A	B	C	D
2	Fund Name
3	Ticker
4	Net Flows
5	Details
6	SPDR S&P 500	SPDR S&P 500	SPY	4,764.58
7	SPY	iShares MSCI Emerging Markets	EEM	2,673.52
8	4,764.58	iShares Russell 2000	IWM	910.17
9	iShares MSCI Emerging Markets	PowerShares QQQ	QQQ	600.83
10	EEM	Market Vectors Gold Miners	GDX	559.23
11	2,673.52	Industrial Select SPDR	XLI	521.68
12	iShares Russell 2000	iShares MSCI Brazil Capped	EWZ	484.01
13	IWM	iShares Core S&P 500	IVV	408.82
14	910.17
15	PowerShares QQQ
16	QQQ
17	600.83
18	Market Vectors Gold Miners
19	GDX
20	559.23
21	Industrial Select SPDR
22	XLI
23	521.68
24	iShares MSCI Brazil Capped
25	EWZ
26	484.01
27	iShares Core S&P 500
28	IVV
29	408.82
Blad13

Cell Formulas
Range	Formula
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))

 

[kytrangho]

Maybe like this.

Please reply.

Blad13

	A	B	C	D
2	Fund Name
3	Ticker
4	Net Flows
5	Details
6	SPDR S&P 500	SPDR S&P 500	SPY	4,764.58
7	SPY	iShares MSCI Emerging Markets	EEM	2,673.52
8	4,764.58	iShares Russell 2000	IWM	910.17
9	iShares MSCI Emerging Markets	PowerShares QQQ	QQQ	600.83
10	EEM	Market Vectors Gold Miners	GDX	559.23
11	2,673.52	Industrial Select SPDR	XLI	521.68
12	iShares Russell 2000	iShares MSCI Brazil Capped	EWZ	484.01
13	IWM	iShares Core S&P 500	IVV	408.82
14	910.17
15	PowerShares QQQ
16	QQQ
17	600.83
18	Market Vectors Gold Miners
19	GDX
20	559.23
21	Industrial Select SPDR
22	XLI
23	521.68
24	iShares MSCI Brazil Capped
25	EWZ
26	484.01
27	iShares Core S&P 500
28	IVV
29	408.82

<thead>
</thead><tbody>
</tbody>

Worksheet Formulas Cell Formula 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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Wow. That's the result I wanted. Thank you very much! What is the logic or rationale behind that coding?

 

[Oeldere]

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.