Microsoft Office Forums - View Single Post - [Solved] Forumla to find all words over three characters

Guessed · 10-01-2020, 07:32 PM

You can expand on Purfleet's code to add further flexibility (with optional inputs) if you wanted to vary the word length and/or get the nth word out of the result. This code works almost the same way as Purfleet's but it allows some optional inputs as well. Note that this function is stricter on what constitutes a word and it includes hits ONLY on letters and excludes numbers and other characters such as ---- or a-bc

Code:

Function GetWords(SelectedText As range, Optional iLength As Integer = 3, Optional nthPosition As Integer) As String
  Dim arrAllWords() As String, LetterTextCount As Integer, iAscChar As Integer
  Dim arrOutput() As String, OutputText As String
  Dim i As Integer, n As Long
  
  arrAllWords = Split(SelectedText, " ")
  
  For i = LBound(arrAllWords) To UBound(arrAllWords)
    If Len(arrAllWords(i)) > iLength Then
      LetterTextCount = Len(arrAllWords(i))
      For n = 1 To LetterTextCount
        iAscChar = Asc(LCase(Mid(arrAllWords(i), n, 1)))
        If iAscChar < 97 Or iAscChar > 122 Then GoTo SkipWord
      Next n
      OutputText = OutputText + " " + arrAllWords(i)
    End If
SkipWord:
  Next i
  
  If nthPosition > 0 Then
    arrOutput = Split(OutputText, " ")
    If nthPosition > LBound(arrOutput) And nthPosition < UBound(arrOutput) Then
      GetWords = arrOutput(nthPosition)
    Else
      GetWords = OutputText   'fallback if provided nthPosition outside of scope
    End If
  Else
    GetWords = OutputText     'return all words
  End If
End Function

A typical formula using this UDF might be
=GetWords(A1) which returns all words longer than 3 letters
=GetWords(A1,2) which returns all words longer than 2 letters (instead of the default 3)
=GetWords(A1,3,2) which returns the 2nd word longer than 3 letters