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

 

[fiberboysa]

Agreed With Mr. Rick Rothstein, on cell A9999 it gives "aaa 009999" and after that it do not change "aaa" to "aab" instead it gives aaa 0010000.
BTW thanks for your reply as well vikas9385
Thank all three of u for helping me out. Be blessed.

 

[István Hirsch]

Enter this formula in A1 and copy down. Unfortunately, even in the 10^6 row it reaches only ADV9999:

=SUBSTITUTE(ADDRESS(2,COLUMN(AAA1)+INT((ROW()-1)/10000),4),2," ")&TEXT(ROW(A1)-1-INT((ROW(A1)-1)/10000)*10000,"0000")

Note: to convert to AA 0000 delete one A from the brackets.

 

[Rick Rothstein]

Enter this formula in A1 and copy down. Unfortunately, even in the 10^6 row it reaches only ADV9999:

=SUBSTITUTE(ADDRESS(2,COLUMN(AAA1)+INT((ROW()-1)/10000),4),2," ")&TEXT(ROW(A1)-1-INT((ROW(A1)-1)/10000)*10000,"0000")

Note: to convert to AA 0000 delete one A from the brackets.

For the three letter case, that formula would be limited as to the maximum extent the letters could go to (AEA maybe).

 

[István Hirsch]

For the three letter case, that formula would be limited as to the maximum extent the letters could go to (AEA maybe).

As 10000 different number belongs to each letter combination, calculating with the number of rows in Excel 2007, only 1048576/10000 ~ 104 letter combinations are consumed, starting from AAA. That is why the value in row 1048576 (last row) is AEA8575 (the result is the same as with your formula). So this is not a real limit considering the available letter combination (number of columns) goes to XDF in Excel 2007.

 

[Rick Rothstein]

As 10000 different number belongs to each letter combination, calculating with the number of rows in Excel 2007, only 1048576/10000 ~ 104 letter combinations are consumed, starting from AAA. That is why the value in row 1048576 (last row) is AEA8575 (the result is the same as with your formula). So this is not a real limit considering the available letter combination (number of columns) goes to XDF in Excel 2007.

However, what if the OP wants to pick up the count at a number later in the series than AAA 0000 in a different column, worksheet or workbook, perhaps where his list will continue on with the series number continuing its count on from the last number that was used in the previous listing? At least that was the premise I used when I constructed the formula that I posted earlier.

 

[István Hirsch]

However, what if the OP wants to pick up the count at a number later in the series than AAA 0000 in a different column, worksheet or workbook, perhaps where his list will continue on with the series number continuing its count on from the last number that was used in the previous listing? At least that was the premise I used when I constructed the formula that I posted earlier.

That is true, if the OP could somehow obtain a capacity of 175 million cells to get to ZZZ, my formula would work to only XFD (about 165 million cells).

 

[fiberboysa]

Yes I did this by searching on internet and mentioned it earlier post in this tread. BTW thank you

 

[István Hirsch]

Yes I did this by searching on internet and mentioned it earlier post in this tread. BTW thank you

I realize now that you have already tried a similar formula with Substitute and Address functions that failed at XFD. May I know what that formula was?

 

[fiberboysa]

István Hirsch I forgot the formula after this long time but I made a simpler and lengthier version of Mr. Rick Rothstein fromula . Which is...
=IF(AND(MID(A1,2,2)="ZZ",RIGHT(A1,4)+0=9999),CHAR(CODE(MID(A1,1,1))+1)&"AA"&" 0000",IF(AND(MID(A1,3,1)="Z",RIGHT(A1,4)+0=9999),LEFT(A1,1)&CHAR(CODE(MID(A1,2,1))+1)&"A"&" 0000",IF(RIGHT(A1,4)+0=9999,LEFT(A1,2)&CHAR(CODE(MID(A1,3,1))+1)&" 0000",LEFT(A1,4)&TEXT(RIGHT(A1,4)+1,"0000"))))