Formula help needed

[Erin McCoy]

I have a 15 digit number. All the numbers have to remain the same except for the 10th digit and the last digit. The 10th digit needs to go up 1 and the last digit needs to go up 7. If the last digit starts at a 7 and adding 7 makes it 14, the 1 in the 14 needs to be ignored so that the 14th number which is 9 always remains a 9.  What is the formula I need to generate the next numbers?

[#2 · Accepted solution]

How about the 10th digit & the 9th digit?

Assume your 15 digit number on Cell A1.

If they act like 14th & 15th digit

On Cell A2, type

=LEFT(A1,9) & RIGHT(VALUE(MID(A1,10,1))+1,1) & MID(A1,11,4) & RIGHT(VALUE(RIGHT(A1,1))+7,1)

Otherwise,

=TEXT(VALUE(LEFT(A1,10))+1,"0000000000") & MID(A1,11,4) & RIGHT(VALUE(RIGHT(A1,1))+7,1)

[#3]

That worked. It was the first solution you posted.  Thanks!