Spell Check function in an IF statement?

[cmotes]

I have 50,000 rows of text data in column A. No text boxes.

I need to split the data into 2 buckets: (a) cells where 100% of the
text passes Excel spellchecker, and (b) everything else. I need to
sort the data based on what passed spellcheck and what did not.

So I'm trying to get something like this:
if(spellcheck(A1)=spelling_is_right,1,0)

Any suggestions?

Thanks!

 

[ilia]

You probably want a custom-defined function, along these lines:

Public Function SpellCheck(rng As Excel.Range) As Boolean
SpellCheck = Application.CheckSpelling(rng.Text)
End Function

 

[ilia]

Hmm... I just realized that this won't work, as CheckSpelling always
returns true when inside a macro. My only other suggestion would be
to use another instance of the Excel application. It will be
extremely slow, so I would recommend to keep calculations manual, and
return an entire array so as not to create a separate instance of
Excel for each cell you're spell checking. For a range that large, it
will take several minutes to check the spelling in this way.

Public Function SpellCheck(rng As Excel.Range) As Boolean()
Dim i as Long, size as Long
Dim objExcel as New Excel.Application
Dim result() as Boolean

size = rng.Cells.Count
ReDim result(1 to size)

for i = 1 to size
result(i) = objExcel.CheckSpelling(rng.Cells(i).Text)
next i

SpellCheck = result()
objExcel.Quit
End Function

You have to array-enter the function, so for example if you're
checking A1:A50000, you'd select B1:B50000 and use this formula:

=SpellCheck(A1:A50000)

Instead of pressing Enter, press Ctrl+Shift+Enter. Expect this to
take a while - a run of 10,000 cells takes 20 seconds.