Sequential alphanumeric cell value

[DataMan123]

I need a formula to follow a numbering from a designated cell and increase by a letter.
Like this:
if cell A1 is 161201A then cell A2 is 161201B and cell A3 is 161201C
and if possible to continue to 161201AA, 161201AB, 161201AC...
Anyone?

 

[Rick Rothstein]

Preferably an open ended formula would be nice, though rarely has the sequence pasted 'AR'.

Well, AR equates to only 44 rows, so István's solution will be way more than enough as it will handle up to 16,386 rows of data. I would suggest, one small change to the formula he posted. Instead of this...

=SUBSTITUTE(161201&ADDRESS(1,ROW(C1),4)&"ß","1ß","")

I would use this...

=SUBSTITUTE(161201&ADDRESS(1,ROWS($1:1),4)&"ß","1ß","")

as it will withstand your inserting rows before Row 3 (where you currently have the first value) without changing the entire series of values (if you insert at Row 3, you would have to copy the first formula down with either formula).

 

[István Hirsch]

István

Worked perfectly!
=SUBSTITUTE(161201&ADDRESS(1,ROW(C1),4)&"ß","1ß","")
It seems the ROW() needs only any cell reference to function as I need; regardless of the value of referenced cell.
Many thank and Merry Christmas!

You are welcome, thanks for the feedback.

 

[István Hirsch]

Well, AR equates to only 44 rows, so István's solution will be way more than enough as it will handle up to 16,386 rows of data. I would suggest, one small change to the formula he posted. Instead of this...

=SUBSTITUTE(161201&ADDRESS(1,ROW(C1),4)&"ß","1ß","")

I would use this...

=SUBSTITUTE(161201&ADDRESS(1,ROWS($1:1),4)&"ß","1ß","")

as it will withstand your inserting rows before Row 3 (where you currently have the first value) without changing the entire series of values (if you insert at Row 3, you would have to copy the first formula down with either formula).

Thanks Rick for the useful suggestion.