Alpha-Numeric Sequential Log problem - nested if. 1

[creeker69]

Hello All,

I hope I can explain this correctly. I need a sheet to create a Log of License plates when we fill in just one cell.
The plates are always 7 characters with the first three being ALPHA (ABC) and the last four characters being numbers. (1234).

My problem is when the plates are numbered with leading zeroes, like ADW0001.

I place the starting plate number in A4, then in A5 on down I drag this formula:

IF(MID(A4,4,3)="000",LEFT(A4,3)&"000"&(RIGHT(A4,1)+1),IF(MID(A4,4,2)="00",LEFT(A4,3)&"00"&(RIGHT(A4,2)+1),IF(MID(A4,4,1)="0",LEFT(A4,3)&"0"&(RIGHT(A4,3)+1),LEFT(A4,3)&(RIGHT(A4,4)+1))

Same Formula split for easier reading:
IF(MID(A4,4,3)="000",
LEFT(A4,3)&"000"&(RIGHT(A4,1)+1),
IF(MID(A4,4,2)="00",
LEFT(A4,3)&"00"&(RIGHT(A4,2)+1),
IF(MID(A4,4,1)="0",
LEFT(A4,3)&"0"&(RIGHT(A4,3)+1),
LEFT(A4,3)&(RIGHT(A4,4)+1

These starting plate numbers work fine and fill the entire sheet properly: AAA1000, AAA0100, AAA0010, AAA1235, AAA0155, AAA0029, etc.

These plate numbers will cause an issue AAA0000, AAA0099, AAA0001

Example of AAA0099
PLATE # TEMP TAG CUSTOMER NAME ISSUED EXPIRES BY
AAA0099 <-Cell A4
AAA00100 <-Next plate should be AAA0100 (7 characters)
AAA001 <-Every plate after is wrong.
AAA002

-or-

PLATE # TEMP TAG CUSTOMER NAME ISSUED EXPIRES BY
AAA0000 <-Cell A4
AAA0001
AAA0002
AAA0003
AAA0004
AAA0005
AAA0006
AAA0007
AAA0008
AAA0009
AAA00010 <- Value should be AAA0010
AAA0001 <- Value should be AAA0011

I think this is more of a logic error on my part. Thing is, I thought it was fixed until we actually had plates start AAA0000.

Any help would be appreciated.

Thanks in advance,
Creeker69

 

[dhookom]

Try:

=LEFT(A4,3)&RIGHT("000"&VALUE(RIGHT(A4,4))+1,4)

Duane
Hook'D on Access
MS Access MVP

 

[zelgar]

The following should work for any item with preceding zeros:

=LEFT(A4,3)&TEXT(VALUE(RIGHT(A4,4))+1,"0000")

 

[creeker69]

zelgar, that will, indeed, work! dhookom, I couldn't get yours to work (but that could be me!).

I thank both of you for your quick responses.

Have a great day!
Creeker69