MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fill Serial - AA, AB,AC...,BA,BB,BC...ZZ


Posted by manoj on September 06, 2001 10:50 PM

Hi

I want to fill
AA
AB
AC
.
.
.
BA
BB
BC
.
.
.
CA
CB
CC

till ZZ

Typing AA in A1 & the next will be filled automatically by just dragging the cells

How can i do it?

Regards,
Manoj


Posted by Juan Pablo on September 07, 2001 7:36 AM

Copy this formula in A2

=CHAR(IF(CODE(RIGHT(R[-1]C,1))=90,CODE(LEFT(R[-1]C,1))+1,CODE(LEFT(R[-1]C,1))))&CHAR(IF(CODE(RIGHT(R[-1]C,1))=90,65,CODE(RIGHT(R[-1]C,1))+1))

and Copy down till you get the ZZ.

Juan Pablo

Posted by Aladin Akyurek on September 07, 2001 10:50 AM

following Juan Pablo's idea of using a formula,

in A1 enter: AA
in A2 enter: =IF(RIGHT(A1)="Z",CHAR(CODE(LEFT(A1))+1),LEFT(A1))&IF(RIGHT(A1)<>"Z",CHAR(CODE(RIGHT(A1))+1),CHAR(65)) [ copy down this as far as needed ]

Aladin