cell reference math

[Brian]

I have a long column of numbers (let's say more than 1000).
I would like to add a second column of numbers with no
spaces between rows that is an extraction of every xth
(e.g. every tenth) number from the first column. How do I
do this without extensive copy, paste special, etc.
manipulations?

Many Thanks,

Brian

 

[Dan E]

If it was column A and started in row 1 in B1 put
=MID(A1, 10, 1) and copy down

Dan E

 

[Matthew Connor]

I have a long column of numbers (let's say more than 1000).
I would like to add a second column of numbers with no
spaces between rows that is an extraction of every xth
(e.g. every tenth) number from the first column. How do I
do this without extensive copy, paste special, etc.
manipulations?

Many Thanks,

Brian

One way - you can use the Offset function to do this. If your column
of numbers starts in A1, put this is in B1:
=OFFSET($A$1,10*ROW() -1,0)


If your second column doesn't start in the first row, you'll need a
more complicated formula, such as:
=OFFSET($A$12,10*(ROW()-ROW(B$12)+1)-1,0)

where B$12 is the first line of your second column.

Regards,

Matthew

 

[Brian]

Thanks Dan for the post. Your solution didn't seem to work.
Don't know why...See Matt's post. It is a really great
function.

Brian