Get in column B the words of column A that are `not in dictionary`

Question

How could I create a macro that would check each cell of column A, find the words that are not in the defined dictionary, and write them (separated by space) in the next cell. In the picture below you can see an example of the worksheet after that macro was completed.

The complete idea was to get a (varchar) column from a database and use excel to spell check it. The next step would be to send an e-mail to the user in charge, containing the rows that contain at least one word in column B (along with the column id, of course). I think that I could do the rest of the work, except this step of getting the erroneous words. If you can think of another idea to spell check a db column, I would be grateful if you shared it with me. Thanks.

Answer 1

Don't do this

This is a crazy idea. Excel is not the right tool for this.

Having said that, it might be doable.

• First you will have to split the 'sentences' into words. This can be done using VBA into a separate sheet.
• Then you can check whether each word exists using COUNTIF or VLOOKUP. For instance if you have a sheet called dictionary containing all valid words in alphabetical order (in the first column), the most efficient way would be =IF(VLOOKUP(B2;dictionary!A:A;1)<>B2;B2;"") for a word in B2.
• Then you can concatenate the resulting cells for each sentence, or just find the first one (using VLOOKUP again, now with an extra argument FALSE).

But... Just forget it!

Answer 2

You can use VBA to call the built-in Office dictionary using Application.CheckSpelling

This is the syntax:

Function CheckSpelling(Word As String, [CustomDictionary], [IgnoreUppercase]) As Boolean

... and here is an example that meets your requirements:

Option Explicit

Public Sub Checker()

Dim s           As Variant
Dim sArray      As Variant
Dim lCurrRow    As Long
Dim lStartRow   As Long
Dim lEndRow     As Long

lStartRow = 1
lEndRow = 5

Application.ScreenUpdating = False

With ThisWorkbook.Worksheets(1)
    'Clear existing data in Column B
    Call .Columns(2).ClearContents

    For lCurrRow = lStartRow To lEndRow

        'Populate an Array, splitting the song title at the spaces
        sArray = Split(.Cells(lCurrRow, 1).Text, " ")

        'Loop through each word in the Array
        For Each s In sArray
            'Spell Check against the main MS Office dictionary
            If Not Application.CheckSpelling(s) Then
                'this word is missing, output to Column B:
                .Cells(lCurrRow, 2).Value = Trim(.Cells(lCurrRow, 2).Value & " " & s)
            End If
        Next s

    Next lCurrRow
End With

Application.ScreenUpdating = True

End Sub