wilkisa
Well-known Member
- Joined
- Apr 7, 2002
- Messages
- 657
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
I have the following in cell A1:
001-001-1
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:
001-001-1
001-002-4
001-003-7
001-004-0
001-005-3
.
.
.
001-999-x
002-000-x
002-001-x
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 000-000-0 but this doesn't work either.
Help, anyone, please?
001-001-1
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:
001-001-1
001-002-4
001-003-7
001-004-0
001-005-3
.
.
.
001-999-x
002-000-x
002-001-x
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 000-000-0 but this doesn't work either.
Help, anyone, please?