Increment values within cells in Excel

[safarijoe2000]

Hello,

I'm sure this is an old question, but I need a bit of help.

Basically, I have a value (Bin Number) that increases by one, both numerically and alphabetically.

For example:

Cell A1 = R1LA01
Cell A2 = R1LA02
.... and so on through to R1LA40

After that on:
Cell A41 = R1LB01
Cell A42 = R1LB02
.... and so on through to R1LY40


As you can tell entering these values will be very time-consuming. So, I was wondering if you have a better solution, as in a formula that I use.

I appreciate any help.

Thanks

 

[Zathras]

Try this:
[blue]

A1: 'R1LA01
A2: =IF(RIGHT(A1,2)<&quot;40&quot;,LEFT(A1,4)&RIGHT(100+RIGHT(A1,2)+1,2),LEFT(A1,3)&CHAR(CODE(MID(A1,4,1))+1)&&quot;01&quot;)

[/color]
Copy the formula from A2 into A3 thru A1000

 

[SkipVought]

Hi,

Here's a solution...

=&quot;R1L&quot;&CHAR(INT((ROW()-1)/40)+65)&IF(MOD(ROW()-1,40)+1<10,&quot;0&quot;,&quot;&quot;)&MOD(ROW()-1,40)+1

Enter in A1 and copy down as far as needed.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[safarijoe2000]

They both worked nicely !

Thank you.

 

[KenWright]

You don't say what version of excel you are using, but in both 2000 and 2002 you can simply put RILA01 in one cell, RILA02 in the next, select them both and then drag down using the small black cross at the botton right of the second cell, and it will autofill, incrementing as it goes.

Regards
Ken..............

 

[Zathras]

Ken: He would have to do that 25 times to get all 1000 codes that are wanted. (Do the first 40, then restart with R1LB01 for the next 40, then R1LC01 etc.)

 

[KenWright]

Doh!!!!!!!! - Serves me right for not reading the question properly - Thanks for picking me up on that Zathras - My apologies to the OP - I'll go back to sleep :-(

Regards
Ken..............