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 (http://www.vbaexpress.com/forum/faq.php?s=&do=search&q=attachment&titleandtext=1&match=all) 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 Project18758

[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-programming-vba-macros/1178967-macro-for-removing-2-letters-words.html