Serial Number increase with letters and numbers

[poorexceller]

Hi Ladies and Gents,

I have been reading through some of the older questions and couldn't put together my answer without asking for help.

Basically what I am looking to do is take a serial number and have it increase by one. The problem however is that the number ends with a letter not a number.

I.e

aba00001a is the first. I need it to go to:
aba00002a
aba00003a
....
aba02001a

Then once I complete that it would restart at
aba00001b etc etc.

I am assuming if I can figure out the first part with the A ending I would just reapply it with a B at the end.

Can anyone help me get a formula in place to complete this task? Or perhaps guide me on where to find it, I may be over thinking this...

Thanks everyone

 

[njimack]

Welcome to the board.

Assuming your formatting is constant (i.e 3 letters, 5 numbers, 1 letter), try this:

=LEFT(A1,3)&TEXT(MID(A1,4,5)+1,"00000")&RIGHT(A1)

 

[poorexceller]

Welcome to the board.

Assuming your formatting is constant (i.e 3 letters, 5 numbers, 1 letter), try this:

=LEFT(A1,3)&TEXT(MID(A1,4,5)+1,"00000")&RIGHT(A1)

Hey njimack,

I actually messed up there are 6 numbers so it would be...aba000001a, would that change the formula?

 

[njimack]

Hey njimack,

I actually messed up there are 6 numbers so it would be...aba000001a, would that change the formula?

A couple of minor changes required...

=LEFT(A1,3)&TEXT(MID(A1,4,6)+1,"000000")&RIGHT(A1)

 

[poorexceller]

A couple of minor changes required...

=LEFT(A1,3)&TEXT(MID(A1,4,6)+1,"000000")&RIGHT(A1)

This worked very well. Thank you so much for the help Neil

 

[BenMiller]

I'm not sure why you're reading data from A1 here, but if you put this anywhere and drag it down, it will cycle through all the number with a at the end, then when it reaches the end of the numbers - 999999 - it will start the numbers over and the letter will advance to b:

="aba"&TEXT(MOD(ROW(A1)-1,999999)+1,"000000")&CHAR(INT((ROW(A1)-1)/999999+97))