# LEFT, MID, RIGHT functions help

#### wilkisa

##### Well-known Member
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.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Give this a try:

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

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?

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?

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.

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.

Does this work?

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

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))

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

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.

Replies
2
Views
274
Replies
3
Views
254
Replies
5
Views
279
Replies
2
Views
743
Replies
8
Views
853

1,218,796
Messages
6,144,529
Members
450,551
Latest member
Mouse7681

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back