# 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.

#### Andrew Poulsom

##### MrExcel MVP
To be fair, Seti had it too!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### wilkisa

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

Replies
4
Views
130
Replies
0
Views
124
Replies
12
Views
201
Replies
3
Views
92
Replies
2
Views
104

1,186,318
Messages
5,957,163
Members
438,291
Latest member
Mel2023

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