|It's actually quite simple. It's mainly of matter of keeping your value_if_true and value_if_false arguments for all of the Nested IF's straight. |
First, it checks to see if the Length of the string is 5 characters.
If it is, it checks to see if the second character is a zero. I had to use quotes ("0") since the MID function returns a Text 0 not a numeric 0.
If the second character is a 0, it checks to see if the last 3 characters are NOT "999"
If all of those conditions are TRUE, then it evaluates LEFT(B4,2)&RIGHT(B4,3)+1 returning the A0 and then adding 1 to last 3 characters.
If the <> "999" is FALSE, then it evaluates LEFT(B4,1)&RIGHT(B4,4)+1 returning the A and 1000. As it turns out, LEFT(B4,1)&RIGHT(B4,3)+1 will work also since the leading 0 is ignored anyway.
If the "0" condition wasn't TRUE, then it skips over to the next LEFT(B4,1)&RIGHT(B4,4)+1 and returns A and the four digits plus 1.
Finally, if the Length wasn't 5 (e.g. AZ1234) then it skips all the way to the end (the value_if_false for the IF(LEN...) function) and evaluates LEFT(B4,2)&RIGHT(B4,4)+1 returning AZ and 1234 plus 1.
If you want to follow the formula as it performs its evaluations step by step, click on the cell with the formula and use the Formula Auditing...Evaluate Formula tool.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.