Serial Number With Alphabets With Formula

[fiberboysa]

Hi,
I need a formula that if i drag it down it gives me serial number with alphabets. Like if i paste that formula in cell A1 it give me serial number AAA 0000 and when i drag it down it give me AAA 0001. When serial number reaches AAA 9999 it automatically give me AAB 0000 and it goes on like this till ZZZ 9999.

It should be like this...

AAA 0000
AAA 0001
AAA 0002
.
.
.
AAA 9999
AAB 0000
AAB 0001
.
.
.
ZZZ 9999

Please let me know if its possible with a formula in MS Excel. I am using MS Excel 2013 on Windows 7.
Thank you

 

[XOR LX]

At that rate, and with a "mere" 1048576 rows (even less if you're on Excel 2003 or earlier), I estimate that, by the end of your first column, and assuming that "AAZ..." is followed by "ABA...", etc., you'll reach only "ADZ..." before you run out of rows.

In fact, if you're intending to get all the way up to "ZZZ9999", I think you'll need about 168 columns' worth, each filled to the very last row. That's over 175 million cells' worth of data.

Is this something you are aware of?

Regards

 

[vikas9385]

Add this list in Custom List option then whenever you type AAA0000 and then drag it will give you next number automatically..10x

 

[fiberboysa]

Thank you very much for your replay
Yes i am aware of this and i tried to use Substitute formula with Address formula combined. And ended up with an error at XFD.
If its not possible the can i have a formula that give me two alphabets instead of 3 Like..

AA 0000
AA 0001
AA 0002
.
.
.
AA 9999
AB 0000
AB 0001
.
.
.
ZZ 9999

 

[fiberboysa]

Interesting technique vikas9385 but I thought of this technique but the problem is I will have to make custom lists to all the PCs if i need to use this serial number again on different PC.

 

[vikas9385]

make a template file for it..

or u can use it by adding two extra column in one just drag n drop you can get aa, ab, ac....and in another column use ="000"&I4+1 formula for 001, 002, 003 and then hide both column & in 3rd column concanate first 2 columns.thanx

 

[Rick Rothstein]

Hi,
I need a formula that if i drag it down it gives me serial number with alphabets. Like if i paste that formula in cell A1 it give me serial number AAA 0000 and when i drag it down it give me AAA 0001. When serial number reaches AAA 9999 it automatically give me AAB 0000 and it goes on like this till ZZZ 9999.

Manually put your starting serial number in cell A1 (probably "AAA 0000" with the space and without the quotes, but any valid serial number can be used to start the sequence), then put this formula in cell A2 and copy down...

=IF(RIGHT(A1,4)+0<9999,LEFT(A1,3)&" "&TEXT(1+RIGHT(A1,4),"0000"),IF(MID(A1,3,1)<"Z",REPLACE(LEFT(A1,3),3,1,CHAR(CODE(MID(A1,3,1))+1)&" 0000"),IF(MID(A1,2,1)<"Z",REPLACE(LEFT(A1,2),2,1,CHAR(CODE(MID(A1,2,1))+1)&"A 0000"),CHAR(CODE(MID(A1,1,1))+1)&"AA 0000")))

Of course, I feel obliged to remind you of LOR LX's comments, just for reinforcement.

 

[fiberboysa]

Manually put your starting serial number in cell A1 (probably "AAA 0000" with the space and without the quotes, but any valid serial number can be used to start the sequence), then put this formula in cell A2 and copy down...

=IF(RIGHT(A1,4)+0<9999,LEFT(A1,3)&" "&TEXT(1+RIGHT(A1,4),"0000"),IF(MID(A1,3,1)<"Z",REPLACE(LEFT(A1,3),3,1,CHAR(CODE(MID(A1,3,1))+1)&" 0000"),IF(MID(A1,2,1)<"Z",REPLACE(LEFT(A1,2),2,1,CHAR(CODE(MID(A1,2,1))+1)&"A 0000"),CHAR(CODE(MID(A1,1,1))+1)&"AA 0000")))

Of course, I feel obliged to remind you of LOR LX's comments, just for reinforcement.

Excellent Perfect Marvelous Exactly what i needed. Thank you very much Mr. Rick Rothstein. Your help is very much appropriated.

 

[vikas9385]

type

aaa0001

<tbody>
</tbody>

in a1

then put following formula in below cells
=LEFT(A1,6)&RIGHT(A1,4)+1

 

[Rick Rothstein]

type

aaa0001

<tbody>
</tbody>

in a1

then put following formula in below cells
=LEFT(A1,6)&RIGHT(A1,4)+1

I think you need to test your suggestions before you post them... what do you think your formula will return when the cell before it becomes AAA9999 ?