#### wilkisa

##### Well-known Member

- Joined
- Apr 7, 2002

- Messages
- 657

- Office Version
- 365
- 2016
- 2013

- Platform
- Windows

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?