wilkisa
Wellknown Member
 Joined
 Apr 7, 2002
 Messages
 657
 Office Version

 365
 2016
 2013
 Platform

 Windows
I have the following in cell A1:
0010011
I need a formula that will add 1 to the middle number and 3 to the right number as the formula is copied down.
One problem is that if the right number is >=10, drop the 10s value and display only the 1s value. I think I have this working.
The next problem is that when the middle number >999, it should drop the 1000s value and display only the 000. Then the first number should increment by 1.
For example:
0010011
0010024
0010037
0010040
0010053
.
.
.
001999x
002000x
002001x
The formula I have tried doesn't quite get it and maybe I am making it too hard.
=(LEFT(A1,4))&IF((MID(A1,5,3)+1)>=1000,(MID(A1,5,3)+1)1000,(MID(A1,5,3)+1))&""&IF((RIGHT(A1,1)+3)>=10,(RIGHT(A1,1)+3)10,(RIGHT(A1,1)+3))
I lose the leading zeros when I try this also. I have created a custom format of 0000000 but this doesn't work either.
Help, anyone, please?
0010011
I need a formula that will add 1 to the middle number and 3 to the right number as the formula is copied down.
One problem is that if the right number is >=10, drop the 10s value and display only the 1s value. I think I have this working.
The next problem is that when the middle number >999, it should drop the 1000s value and display only the 000. Then the first number should increment by 1.
For example:
0010011
0010024
0010037
0010040
0010053
.
.
.
001999x
002000x
002001x
The formula I have tried doesn't quite get it and maybe I am making it too hard.
=(LEFT(A1,4))&IF((MID(A1,5,3)+1)>=1000,(MID(A1,5,3)+1)1000,(MID(A1,5,3)+1))&""&IF((RIGHT(A1,1)+3)>=10,(RIGHT(A1,1)+3)10,(RIGHT(A1,1)+3))
I lose the leading zeros when I try this also. I have created a custom format of 0000000 but this doesn't work either.
Help, anyone, please?