LEFT, MID, RIGHT functions help

[wilkisa]

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?

 

[Andrew Poulsom]

To be fair, Seti had it too!

 

[wilkisa]

Absolutely, Andrew. I was looking at the formulas and thought the reply came in twice from the same person! Thanks to you also, Seti!