Forumla to find all words over three characters

Guessed · #8 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to find (highlight) two and more words in a list of 75k single words in Word 2010	Usora	Word	8	05-29-2018 03:34 AM
VBA Find&Replace all bold, itlaic, underlined and highlighted words/characters	Kalü	Word VBA	22	04-24-2018 05:35 AM
How show characters instead words (status bar)	BrunoChirelli	Word	2	02-19-2015 12:03 PM
Color words ending with special characters	Singh_Edm	Word	2	01-20-2014 12:51 AM
Can you check the "find whole words only" box when using characters?	pimpong	Word	6	02-06-2012 06:56 PM