Removing words under 4 letters

Hi, I am trying to figure out how to remove words that are shorter than 4 letters long from my data.

I only have one column starting at A2 (there is a header in A1).

Any help on this ASAP would be greatly appreciated.

Thanks

If you only need to do it once, try inserting this formula in B2: =LEN(A2)
Copy the formula to the last row, then filter for those with 1, 2, 3, and delete accordingly.

I have almost 17,000 rows to do this for. How do I filter to show only the words that are 1,2,3 letters long?

Originally Posted by jeremyh09

I have almost 17,000 rows to do this for.

Try this

Sub test()
    Dim a, i As Long
    With Range("a2", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "\b\S{1,3}\b"
            For i = 1 To UBound(a, 1)
                a(i, 1) = Application.Trim(.Replace(a(i, 1), ""))
            Next
        End With
        .Value = a
    End With
End Sub

Thanks!

I have sentences in each cell, for example: The alligator was swimming in the swamp. I want to remove words shorter than 4 letters so I want it to just display alligator swimming swamp.

Originally Posted by millz

Try using this UDF. Place it in a module in the workbook, and in cell B2, enter: =RemoveWords(A2)
and copy the formula to the last row.

Public Function RemoveWords(ByVal sText As String)
    Dim i As Long, v
    v = Split(sText, " ")
    For i = 0 To UBound(v)
        If Len(v(i)) < 4 Then v(i) = ""
    Next
    RemoveWords = Application.WorksheetFunction.Trim(Join(v, " "))
End Function

When I hit F5 to run it, it says Ambiguous Name Detected RemoveWords.

Not exactly sure why you had to hit F5.

See attached sample.