Macro for removing 2 letters word

[Hao Xu]

Hi guys!:

I need really need your help for this.
I was looking for a macro that could automatically detect and remove 2 letter words (for example: or, if, hg, wa, rt, op...) in a selected range (it will be almost always in column A)

Also, i wanted to put this macro some words that it should not remove (because i work with some products with 2 letter brand such as LG, CH, DG...) I was thinking that i could add a list to this macro.
For example

in and out we went to the top of the hill---------------------> and out went the top hill
of one to many we see an answer------------------------------>one many see answer
df is gh is fe on off----------------------->is is on off ("is" is not removed because i need this word, as well as a list that i have of 2 letters word)


Thank you in advance guys!

Waiting for your replies.

[mdmackillop]

Option Compare Text
Option Explicit


Sub Test()
    Dim Words, W, X
    Dim i As Long
    Dim Reserved As Range, cel As Range
    Dim txt As String

    Set Reserved = [C1].CurrentRegion  'Set to required range

    For Each cel In Selection
        Words = Split(cel, " ")
        For i = 0 To UBound(Words)
            W = Words(i)
            If Len(W) = 2 Then
                Set X = Reserved.Find(W)
                If X Is Nothing Then Words(i) = ""
            End If
        Next

        txt = Join(Words, " ")
        For i = 2 To UBound(Words)
            txt = Replace(txt, String(i, " "), " ")
        Next i
        cel = Trim(txt)
    Next cel
End Sub

[Hao Xu]

Hi Mark:

Thank you for your reply, but this macro is giving me an error of compilation, and it highlights here:

cel = Trim(txt)

(I'm a complete newbie of vba, so i truly thank you for trying to help me)

[mdmackillop]

Can you Post a workbook with sample data?

[snb]

I'd use

Sub M_snb()
  [A1:A2000]=[if(A1:A2000="","",if(len(A1:A2000)=2,"",A1:A2000))]
End Sub

[Hao Xu]

Hi:

Please find the sample here:

[mdmackillop]

Seems OK. Reserved list added to sheet.

[Hao Xu]

That is what i was looking for!!!!!!

How do i get the code of that macro?

Sorry i'm a complete newbie

[mdmackillop]

The code is as Post #2 and appears in Module 1 of the VBA ProjectCapture.jpg

[jolivanes]

Re: Sorry i'm a complete newbie
Even newbies should post the hyperlinks to the other forums where you posted.
https://www.excelforum.com/excel-pro...ers-words.html