How to increment a number located mid string..

[Plasma]

Hello all..

I have this number format: 1655-01-32 and I need to have a formula that will increase the middle number by 1, for example 1655-01-32 incremented to 1655-02-32, then 1655-03-32, etc.

What i wanted to do is type in the first number (1655-01-32), then have it auto increment 9 other times.

Any help would be appreciated.

-David

[dosydos]

Lets say (1655-01-32) is in cell A1.
Then put this formula in Cell A2 and copy down as many times as needed.
=SUBSTITUTE(A1,TEXT(MID(A1,FIND("-",A1)+1,2),"00"),TEXT(MID(A1,FIND("-",A1)+1,2)+1,"00"))

[dosydos]

whoops noticed an error with my 1st equation where it substituted in rare occasions the first 4 digit number. Please use this equation instead in cell A2 and copy down..

Please Login or Register  to view this content.

[Plasma]

Thanks Dosydos.

I am running into a problem. The formula first changed the 1655 to 1656, then for the 2nd time, it starts to change the -01- to -02-

So for me, C1 = 1655-01-32. I paste the formula in C13, then C25, C37, C49etc. C13 shows as 1656-01-32, then C25 shows as 1656-02-32, C37 shows as 1656-03-32, C49 shows as 1657-03-32.

Not sure if that makes sense. but there is a pattern to it lol.

[dosydos]

I just tried doing everything you said in order. I started with 1655-01-32 in C1. I then pasted my 2nd formula in C13. It through up a #Value error as expected. I then changed all "A1" references in the formula to C1. This immediately changed the outcome of the formula to 1655-02-32. I then copied the updated formula in C13 and pasted in C25, C37, and C49. this gave me the output of 1655-03-32 , 1655-04-32 , and 1655-05-32 respectively.