excel - How to count length of a word from a sentence and print the word(s) in the cell?

Question

I want to make a function where I extract all words with length = 2 from a sentence. For example, if the sentence is "The Cat is brown", I want the result in the cell to be "is". If there are multiple words with length = 2, I want to keep these too. I have tried MID, RIGHT, LEFT, etc. These does not work as the position of the word is not always identical.

I have no clue how to do this in VBA, any suggestions are welcome :)

Thanks

Answer 1

I have made you a UDF which should work for what you want. You use it like so:

=ExtractWords(Cell to check, Optional number of letters)

By default it will check for 2 letter words but you can specify as well as shown above.

Here is the code. Place it into a module

Function ExtractWords(Cell As Range, Optional NumOfLetters As Integer)

Dim r As String, i As Long, CurrentString As String, FullString As String, m As String

If NumOfLetters = 0 Then NumOfLetters = 2

r = Cell.Value

For i = 1 To Len(r)
    m = Mid(r, i, 1)
    If Asc(UCase(m)) >= 65 And Asc(UCase(m)) <= 90 Or m = "-" Or m = "'" Then 'Accepts hyphen or single quote as part of the word
        CurrentString = CurrentString & m
        If i = Len(r) Then GoTo CheckLastWord
    Else
CheckLastWord:
        If Len(CurrentString) = NumOfLetters Then
            If FullString = "" Then
                FullString = CurrentString
            Else
                FullString = FullString & " " & CurrentString 'Change space if want another delimiter
            End If
        End If
        CurrentString = ""
    End If
Next i

If FullString = "" Then
    ExtractWords = "N/A" 'If no words are found to contain the length required
Else
    ExtractWords = FullString
End If

End Function

There are probably other ways to do it that may be easier or more efficient. This is just something I came up with.