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?

 

[Jim North]

Give this a try:

=TEXT(SUBSTITUTE(A1,"-","")+13,"000-###-#")

 

[wilkisa]

Thanks, Jim but there is still a problem.

It works great except where the 10s value is added in at the 3rd number group. I cannot skip any numbers in the middle number group but this forces me to.

Example, this is what your formula gives me:

001-001-1
001-002-4
001-003-7
001-005-0 This is where it goes wrong; this value should be 001-004-0. Then next:
001-005-3

Can you give me some additional help with this?

 

[wilkisa]

Thanks, Jim but there is still a problem.

It works great except where the 10s value is added in at the 3rd number group. I cannot skip any numbers in the middle number group but this forces me to.

Example, this is what your formula gives me:

001-001-1
001-002-4
001-003-7
001-005-0 This is where it goes wrong; this value should be 001-004-0. Then next:
001-005-3

Can you give me some additional help with this?

 

[Jim North]

This is a partial solution:

=LEFT(A1,3)& "-" & RIGHT(TEXT(MID(A1,5,3)+1,"000"),3) &"-"& RIGHT(TEXT(RIGHT(A1,1)+3,0),1)

It handles the middle and the end... but I think you will need a macro to handle the front... but I've been proven wrong before.

 

[Seti]

Try this formula:

=TEXT(1+SUBSTITUTE(LEFT(A1,7),"-",""),"000-000")&"-"& RIGHT(TEXT(RIGHT(A1,1)+3,0),1)

It is a combination of previous posts.

 

[Andrew Poulsom]

Does this work?

=TEXT(VALUE(SUBSTITUTE(LEFT(A1,7),"-",""))+1,"000-000")&"-"&RIGHT(RIGHT(A1,1)+3,1)

 

[Jim North]

I think I've got it!!!!

=IF(MID(A1,5,3)="999",TEXT(LEFT(A1,3)+1,"000") & "-" & RIGHT(TEXT(MID(A1,5,3)+1,"000"),3) &"-"& RIGHT(TEXT(RIGHT(A1,1)+3,0),1),TEXT(LEFT(A1,3),"000") & "-" & RIGHT(TEXT(MID(A1,5,3)+1,"000"),3) &"-"& RIGHT(TEXT(RIGHT(A1,1)+3,0),1))

 

[Jim North]

I think Andrew has it...

But to satisfy my pride, this is what I came up with:

=TEXT(LEFT(SUBSTITUTE(A1,"-",""),6)+1,"000-###") &"-" &RIGHT(TEXT(RIGHT(A1,1)+3,0),1)
_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-09-04 10:04

 

[wilkisa]

Actually, Jim, you both have it and I thank both of you very much. However, for the sake of brevity, I will use Andrew's formula.