I've got a long list of text data in column A, and am using the MID command
to extract one bit of data from each which is then inserted into four columns
(B-E) e.g.

B9 =MID(A27,23,2)
C9 =MID(A28,23,1)
D9 =MID(A29,23,5)
E9 =MID(A31,23,5)

When I drag down B9, I want the formula to change by 20 (from A27 to A47). I
have created 25 rows now, but the drag down still won't pick up the pattern
of 20 between cells.

Can anyone tell me how to achieve this?

How about this

=MID(INDEX($A:$A,ROW(A1)*20+6+COLUMN(A1)),2,2)

--

HTH

Bob

"Disco3Stu" wrote in message
...
I've got a long list of text data in column A, and am using the MID
command
to extract one bit of data from each which is then inserted into four
columns
(B-E) e.g.

B9 =MID(A27,23,2)
C9 =MID(A28,23,1)
D9 =MID(A29,23,5)
E9 =MID(A31,23,5)

When I drag down B9, I want the formula to change by 20 (from A27 to A47).
I
have created 25 rows now, but the drag down still won't pick up the
pattern
of 20 between cells.

Can anyone tell me how to achieve this?

One way is to replace the below formula

=MID(A27,23,2)

with

=MID(INDIRECT("A" & 27+(20*(ROW(A1)-1))),23,2)

--
Jacob


"Disco3Stu" wrote:

I've got a long list of text data in column A, and am using the MID command
to extract one bit of data from each which is then inserted into four columns
(B-E) e.g.

B9 =MID(A27,23,2)
C9 =MID(A28,23,1)
D9 =MID(A29,23,5)
E9 =MID(A31,23,5)

When I drag down B9, I want the formula to change by 20 (from A27 to A47). I
have created 25 rows now, but the drag down still won't pick up the pattern
of 20 between cells.

Can anyone tell me how to achieve this?

Thank you, worked a treat (and saved a lot of typing!)

"Jacob Skaria" wrote:

One way is to replace the below formula

=MID(A27,23,2)

with

=MID(INDIRECT("A" & 27+(20*(ROW(A1)-1))),23,2)

--
Jacob


"Disco3Stu" wrote:

I've got a long list of text data in column A, and am using the MID command
to extract one bit of data from each which is then inserted into four columns
(B-E) e.g.

B9 =MID(A27,23,2)
C9 =MID(A28,23,1)
D9 =MID(A29,23,5)
E9 =MID(A31,23,5)

When I drag down B9, I want the formula to change by 20 (from A27 to A47). I
have created 25 rows now, but the drag down still won't pick up the pattern
of 20 between cells.

Can anyone tell me how to achieve this?