Prev: Import multiple photos into excel
Next: AutoFill Changing Wrong Value
From: Disco3Stu on 9 Mar 2010 03:07
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?
From: Bob Phillips on 9 Mar 2010 03:45
How about this

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

--

HTH

Bob

"Disco3Stu" <Disco3Stu(a)discussions.microsoft.com> wrote in message
news:903CD736-EDAA-4161-AB0F-533C1FD2254F(a)microsoft.com...
> 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?


From: Jacob Skaria on 9 Mar 2010 04:14
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?
From: Disco3Stu on 9 Mar 2010 05:35
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?
 | 
Pages: 1
Prev: Import multiple photos into excel
Next: AutoFill Changing Wrong Value