Is there a function to remove word with a certain lenth?

Question

So, I got excel sheets with bunch of words seperated with space bar blanks. So the row goes like this:

excel write a sheep e shave shovel with no g life less f
...
...

My question is, is there a way to remove those certain lenth of words? like, if I want to delete words with a length of 1, result should be like this :

excel write sheep shave shovel with no life less
...
...

I'm not familiar with VBA so, I want to ask if there is a way to do it with a fomular Hope you guys can help me.

Thanks !

Answer 1

If one has TEXTJOIN:

=TEXTJOIN(" ",,FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","//b[string-length(.)>1]"))

If one does not have TEXTJOIN put this UDF in a module attached to the workbook:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0
    
    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Using:

=TEXTJOIN(" ",TRUE,FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","//b[string-length(.)>1]"))

Confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Answer 2

If FILTERXML and TEXTJOIN functions are available to you then you can use below formula assuming source data is in cell A1

=TEXTJOIN(" ",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[string-length()>1]"))

For FILTERXML function you can refer below excellent reference:

Excel - Extract substring(s) from string using FILTERXML

Answer 3

Here is a VBA User Defined Function:

Public Function ytrewq(s As String) As String
    Dim arr, a, temp As String
    arr = Split(s, " ")
    temp = ""
    For Each a In arr
        If Len(a) > 1 Then
            temp = temp & " " & a
        End If
    Next a
    
    ytrewq = Mid(temp, 2)
End Function

use it like:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

(this can be used if your version of Excel does not support either TEXTJOIN() or FILTERXML()).

Answer 4

You can use the function Replace , but first use LEN to decide how long your text is and check where your replacements with FIND

Answer 5

You can use first LEN to check the length of your text , use FIND to check where is the charcters inside the text Than use REPLACE , to replace those with a new text .