LEFT, MID, RIGHT functions help

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Does this work?

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

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,363
Messages
5,723,919
Members
422,527
Latest member
JayTheKaz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top