Forum Discussion
Solved
Formula help needed
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?
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)
2 Replies
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)
That worked. It was the first solution you posted. Thanks!
