I got it right, by focusing on the WHERE clause (that alone dictates the number of rows), and hoping that there was no parentheses mismatch or similar error buried in the code.
The code seems overly complicated to me. While I agree that it is "one of several examples of practical use of Tally table.", I would much rather see a GOOD example of practical use of a Tally table.
Here is a much simpler and easier to understand way to get the same results. Note that I adapted this code from a snippet taken from Erland Sommarskog's website.
DECLARE @Text NVARCHAR(500);
DECLARE @StringDelimiter CHAR(1);
SET @Text = 'This T-SQL will split these sentences into rows.' +
'How many rows will be returned?.' +
'M.a.y.b.e..n.o.n.e.?';
SET @StringDelimiter = '.';
-- Add delimiter before and after text;
-- This removes the need for special code to handle start and end of string.
DECLARE @TextPlus NVARCHAR(502)
SET @TextPlus = @StringDelimiter + @Text + @StringDelimiter;
WITH Tally(Number) AS
(SELECT 1 AS Number
UNION ALL
SELECT Number + 1 AS Number
FROM Tally
WHERE Number < LEN(@TextPlus))
SELECT SUBSTRING(@TextPlus,
Number + 1,
CHARINDEX(@StringDelimiter, @TextPlus, Number + 1) - Number - 1) AS SPLIT
FROM Tally
WHERE Number <= LEN(@TextPlus) - 1
AND SUBSTRING(@TextPlus, Number, 1) = @StringDelimiter
ORDER BY Number
OPTION (MAXRECURSION 0);