Formula to copy across columns for football comp Cell reference error

[Alpacino]

Hi all,

Wonder if you can help.

Trying to find a formula that be copied across columns to split a string containing numbers into two adjest columns. however to my frustration when I try to copy the formula it doesn't pull the correct cell reference.

Can anyone help with the formula. Basically I am using Value(Mid(B2,2,1)) for home and away Value(Right(B2,1))

Thank you in advance

Bob

Claire

Dave

Bill

Fixture

Bob

Claire

Dave

Bill

H

A

H

A

H

A

H

A

QPR v Stoke

H1A2

H1a1

H1a2

H1A1

1

2

Aston villa v Arsenal

H0A2

H1a0

H1a3

H1A2

0

2

Newcastle v Hull

H1A2

H1a3

H1a3

H1A1

1

2

Burnley V Sunderland

H1A1

H0a0

H1a0

H2A2

1

1

Swansea v Southampton

H2A2

H0a2

H2a2

H2A1

2

2

Tottenham v West Brom

H2A2

H2a1

H3a1

H3A1

2

2

West Ham v Liverpool

H1A3

H1a3

H2a3

H1A2

1

3

Leicester v Man United

H1A1

H0a2

H1a4

H1A3

1

1

Everton v Crystal Palace

H2A0

H3a1

H3a1

H3A1

2

0

Man City v Chelsea

H2A2

H1a2

H1a1

H1A1

2

2

<tbody>
</tbody><colgroup><col><col span="4"><col><col span="8"></colgroup>

 

[BobUmlas]

There's no way to get the pattern b,b,c,c,d,d,e,e by filling right. Easiest would be to manually enter these formulas into the first row, then fill down. It COULD be done, but the formula would be somewhat involved:
For the first:
=1*MID(INDEX(2:2,INT(COLUMN()/2)-1),2,1)
for the 2nd:
=1*RIGHT(INDEX(2:2,INT(COLUMN()/2)-1),1)
select both these formulas and fill right.