Unable to decipher what is wrong with the loop

As you do not ever need an array neither any loop !
Just using an easy Excel Basics worksheet function (aka PROPER) even under VBA …

I'm sorry I was not being clear, the examples I gave were just simple examples.
I know I can use PROPER no biggie, and if there is some number use UPPER.

But it's not that simple. I have strings that are compounded words, for example "JOHNDATA" which I want the function to translate to JohnData.
I also have short strings that are acronyms like, "MBC" which I want the function to translate to MBC. But I also have short strings that are not acronyms like, "THE", "FOR" which I want the function to translate to The and for respectively. I also have four-letter and two-letter strings like "BANK" "OF" "ROCK" "MCIO" which should end up in different CASES.

What I plan the function to do are:
1) Divide the string into words
2.1) Check against a database for words with unique cases (JohnData) so I can add into the database in the future, any word that comes up and is unique
2.2) Check if a word is purely letters or has numbers; each have their own set of rules and filters
2.3) For purely letters, run against another database that filters words like the, for, of etc. that are not to be put in a PROPER case
3) Apply the correct case for each word
4) Concatenate each word back into a string

Currently, I'm stuck at number 1 because the loop won't work and I can't extract each word into one array slot.

Hope I made myself clear. If you do not believe that I tried making this work with just basic Excel functions, just look at my 3,994 character formula. (Note, I had longer than this one.)

Thank you for that SPLIT function. Didn't know that exist.

But I still want an answer as to why my loop is not working.
For educational purposes. Hehehe