在列B中列出“不在字典”中的列A的单词 [英] Get in column B the words of column A that are `not in dictionary`

问题描述

如何创建一个检查列A的每个单元格的宏，找到不在定义的字典中的单词，并在下一个单元格中写入（以空格分隔）。在下图中，您可以看到该宏完成后的工作表示例。

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.

完整的想法是从数据库中获取（varchar）列，并使用excel来拼写检查。下一步是发送电子邮件给负责的用户，其中包含列B中至少包含一个字的行（当然也包括列ID）。我认为我可以做其余的工作，除了这个错误的话的步骤。如果您能想到另一个想法来拼写检查数据库列表，我将不胜感激您与我分享。谢谢。

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.

推荐答案

您可以使用VBA使用 Application.CheckSpelling 调用内置的Office字典

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

这是语法：

功能 CheckSpelling （ Word As String ，[ CustomDictionary ]，[ IgnoreUppercase ]）作为布尔值

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

这篇关于在列B中列出“不在字典”中的列A的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！