Serial Number With Alphabets With Formula

fiberboysa

Board Regular
Joined
Apr 25, 2012
Messages
90
Office Version
  1. 2019
Platform
  1. Windows
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 :)
 

Some videos you may like

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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Aug 29, 2009
Messages
96
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
Joined
Apr 25, 2012
Messages
90
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 25, 2012
Messages
90
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 29, 2009
Messages
96
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
Joined
Apr 18, 2011
Messages
36,333
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 25, 2012
Messages
90
Office Version
  1. 2019
Platform
  1. Windows
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 :eek::eek::eek: Exactly what i needed. Thank you very much Mr. Rick Rothstein. Your help is very much appropriated. :)
 

vikas9385

Board Regular
Joined
Aug 29, 2009
Messages
96
type
aaa0001

<tbody>
</tbody>

in a1

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,333
Office Version
  1. 2010
Platform
  1. Windows
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top