Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #8  
Old 10-01-2020, 07:32 PM
Guessed's Avatar
Guessed Guessed is offline Forumla to find all words over three characters Windows 10 Forumla to find all words over three characters Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 4,185
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
 

Thread Tools
Display Modes


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
Forumla to find all words over three characters VBA Find&Replace all bold, itlaic, underlined and highlighted words/characters Kalü Word VBA 22 04-24-2018 05:35 AM
Forumla to find all words over three characters How show characters instead words (status bar) BrunoChirelli Word 2 02-19-2015 12:03 PM
Forumla to find all words over three characters 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:56 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft