Basic Question About Copying Cells

[angus---]

I've got a column of cell data with a mixture of different data types: dates, text, numbers, etc.

I am interested in only every fifth field, which happens to be number. I'd like to be able to enter a formula into a cell which will copy the fifth entry into a new column. I've got a ton of stock data that I need to parse this way. So, for example if there are 1000 entries in the column, I would need to extract 1000/5 = 200 fields which would be copied into a new column with the 200 values.

If you guys could explain how to do this, I'd be grateful thanks.

-Angus

[Special-K]

Use Text to columns and select the appropriate delimiter, e.g. space or comma etc

[angus---]

Thanks for reply Special-K. I probably should have said that each value is in its own cell, so there are no delimiters (that I'm aware of). Let me clarify. For example, if the column of data looked like this:

A1: 4556
A2: 2993
A3: 3882
A4: 2883
A5: 8382
A6: 2939
A7: 2933
A8: 5858
A9: 2093
A10: 5955

I would need to be able to copy the data in A5 into B1 and the data in A10 into B2, and so on. I've got lots of data to crunch through. Any ideas?

[Fotis1991]

In B1.

=A1+4

In B2.

=B1+5

Copy down.

[angus---]

A1+4 just adds 4 to the contents of A1. In my example that would be equal to 4560, instead of the correct answer which is the value in cell A5, 8382. I need a function that would address row A5, get the contents, and place them into B1.

I am wondering if I have to jump into VBA to do the operation that I've described?

[bebo021999]

Assuming your data in A1:A1000
In B1:

Please Login or Register  to view this content.

Copy down to B200

[bebo021999]

In general, If you start from row n, try: INDEX(An:Ax,(ROW()-n+1)*5)

[excelxlexcel]

try formula in b col, INDIRECT("a"&(ROW()*5))

revert with result

[excelxlexcel]

try formula , INDIRECT("a"&(ROW()*5)), in B col

revert with result

[angus---]

Thanks excelxlexcel and bebo021999. I used a modified version of your suggested INDEX function to get the correct values into the correct cells. Thx.

angus---