Subtract 1 from a character in a number

[afrantz]

Good day, all

I need a formula that will allow me to subtract, and sometimes add, the 13th character by 1.

So this number here:
22222-222-A22ZQ

I need to change to:
22222-222-A21ZQ

And sometimes (if it matters), I'll need to change this number:
22222-222-A22

To this number:
22222-222-A21

I've done a lot of searches using terms like "Excel subtract a number in a string by one -date -convert" and various combinations of this but can't find anything like what I want. I realize this function may be unusual and possibly simple.

Thank you!

 

[Jonmo1]

If it's always the 13th character, try

=REPLACE(A1,13,1,MID(A1,13,1)-1)

Now what if the 13th character was a 0 ?
Or a 9 if you're ADDING instead of subtracting ?

 

[afrantz]

It can sometimes be the 14th but mostly the 13th. And the formula would not be needed if the 13th character was a 0. Only if it's greater than 0.

That formula works! Thank you!

 

[pjmorris]

Assuming its the 13 character then try this: =LEFT(C1,11)& (--MID(C1,12,2)+A1) & MID(C1,14,255) I've assumed that the 22 (12th&13th characters) are a single number and hence can be incremented or decremented at will, I've also assumed that the number you want to increment/decrement by is in A1.

 

[Jonmo1]

You're welcome.

 

[afrantz]

Thanks Peter, I'll test and make a note of that formula as well. I appreciate it.

 

[pjmorris]

no problem, my pleasure to have helped and thatnks for the feedback.