# Serial Number With Alphabets With Formula

#### fiberboysa

##### Board Regular
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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### XOR LX

##### Well-known Member
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

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

#### fiberboysa

##### Board Regular
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

##### Board Regular

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

##### Board Regular
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

##### MrExcel MVP

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...

Code:
``=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

##### Board Regular
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...

Code:
``=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

##### Board Regular
type
 aaa0001

<tbody>
</tbody>

in a1

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

#### Rick Rothstein

##### MrExcel MVP
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 ?

Replies
2
Views
52
Replies
2
Views
38
Replies
4
Views
78
Replies
27
Views
290
Legacy 456155
L
Replies
1
Views
41

1,109,348
Messages
5,528,182
Members
409,807
Latest member
nicky736

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...