sequencing letters

[mattyb]

I need to create a sequence of letters to be used in an alpha based identification code. I want the user to key in one code (ie PREAAAA ) where the frist four characters are constant and the last three build in sequential order. Example would be
1-PREAAAA
2-PREAAAB
3-PREAAAC
.....
n-PREAABA
.....
n-PREAACA
.....
Any thoughts on a formula that can do this?
Mattyb

 

[schielrn]

I have this, but it does not work for anything ending in Z after the first one and unfortunately I do not have the time at the moment to look in depth into this:

=IF(LEN(SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""))=1,"PREAAA"&SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""),IF(LEN(SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""))=2,"PREAA"&SUBSTITUTE(ADDRESS(1,INT(ROW(A1)/26),4),"1","")&SUBSTITUTE(ADDRESS(1,MOD(ROW(A1),26),4),"1",""),"PREA"&SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1","")))

Hope that helps.

 

[texasalynn]

try putting this into a cell
="PREAA"&char(row(a65))

then when you copy down it will sequence from A to Z

 

[schielrn]

Actually I looked at it a little more and noticed a flaw with one thing and fixed that but still have the problem with it ending in Z because of how mod behaves, but could probably insert an if statement to fix that:

=IF(LEN(SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""))=1,"PREAAA"&SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""),IF(LEN(SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1",""))=2,"PREAA"&SUBSTITUTE(ADDRESS(1,INT(ROW(A1)/26),4),"1","")&SUBSTITUTE(ADDRESS(1,MOD(ROW(A1),26),4),"1",""),"PREA"&SUBSTITUTE(ADDRESS(1,INT(ROW(A1)/676)+1,4),"1","")&SUBSTITUTE(ADDRESS(1,MOD(ROW(A1),676),4),"1","")))

 

[pgc01]

Hi

Another option. In A2:

="PREA"&CHAR(CODE("A")+INT((ROWS($A$2:A2)-1)/676))&CHAR(CODE("A")+MOD(INT((ROWS($A$2:A2)-1)/26),26))&CHAR(CODE("A")+MOD(ROWS($A$2:A2)-1,26))

Copy down

 

[mattyb]

All the assistance is much appreciated.

 

[nbrcrunch]

if you're willing to entertain hex numbers:

=LEFT("000",LEN("000")-LEN(TEXT(DEC2HEX(ROW()),"000")))&(TEXT(DEC2HEX(ROW()),"000"))