You say it works if middle initial has no period, but for line 9 it did not find a period, so the error return was a null, not the "R". The non-error return was the result of the formula, whose MID value suffered from the failed FIND getting a #VALUE! error -- at which point the -1,2 part is meaningless.

If I'm right so far, then line 10 also suffers because it depends on finding a period.