Find position of blank space

I have this in column A:
Mary*Spaunhorst*Ballmann
John Adelhardt
Magelene*Porst*Adelhardt
Elisabeth*Perfling*Altemueller
Ferdinand Altemueller
William J. Altemueller
Frances*Boehm*Ballmann

I used this formula in cell B1:=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) and copied it down

I am getting the following in column B: #VALUE!
5
#VALUE!
#VALUE!
10
11
#VALUE!

I suspect something hidden is in the cell. How do I figure out what is going on? What do I need to do to not get the #VALUE?

In Excel I do not see the * that is displayed above. What can I do to fix the data?

Please Login or Register  to view this content.

These 2 formulas work

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread you highjacked, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking

As you already had an answer I created the new thread for you Please read forum rules