How to automatically fill range in excel by interpolating between lower and upper bounds

Question

I want to interpolate between A00-B99 and have excel expand the series to A00, A01, A02,.... all the way to B99.

SO if I have A00 in one cell and want to have excel automatically fill down to B99, how would I do this?

Thanks!

Answer 1

in the first cell put:

=LEFT(A1,FIND("-",A1)-1)

or just put the first value:

Then referring to that cell and the cell with the range use:

=IF(OR(C1=RIGHT($A$1,3),C1=""),"",IF(RIGHT(C1,2)+1=100,CHAR(CODE(LEFT(C1))+1)&"00",LEFT(C1)&TEXT(RIGHT(C1,2)+1,"00")))

And copy down the column.

Note: This is not a silver bullet. It is based on there being one Letter and two numbers in the setup. Any other combination and this will fail.

Answer 2

In A1 enter:

=IF(ROW()<101,"A"&TEXT(ROW()-1,"00"),"B"&TEXT(ROW()-101,"00"))

and copy downwards

Answer 3

Put this at the top of a column (or wherever you want to start the sequence) and fill down.

=TEXT(MOD(ROW(1:1)-1, 100), "\"&CHAR(INT((ROW(1:1)-1)/100)+65)&"00")

Answer 4

There is all ready a forum post relating to your post. You can check it out here: How to automatically fill range by interpolating between lower and upper bounds