Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-30-2020, 10:01 AM
14spar15 14spar15 is offline Forumla to find all words over three characters Windows 7 64bit Forumla to find all words over three characters Office 2010 64bit
Advanced Beginner
Forumla to find all words over three characters
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default Forumla to find all words over three characters

Hello, I need a formula to give me all of the words (contains letters only - no numbers) in a cell that have over three letters. If possible I only want these words that are after the last “-“ in this cell. If not, I can first just trim the string after the last “-“ but I would like to see how the two could be combined. For example “lizard squirrel - bird - mouse dog cat skunk23 alligator“ would return “mouse alligator “. I’ve been looking all morning and can’t find anything close enough to figure this out. Thank you
Reply With Quote
  #2  
Old 09-30-2020, 11:25 AM
Purfleet Purfleet is offline Forumla to find all words over three characters Windows 10 Forumla to find all words over three characters Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I think a formula would be very messy and long, how about a bit of vba?
Reply With Quote
  #3  
Old 09-30-2020, 04:05 PM
14spar15 14spar15 is offline Forumla to find all words over three characters Windows 7 64bit Forumla to find all words over three characters Office 2010 64bit
Advanced Beginner
Forumla to find all words over three characters
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Thanks so much for the help here. This looks like it could work for me. What I'm doing is pasting a string into column A and this goes through about 15 formulas that will manipulate the original string into what I need in column T. Part of this process does use vba that looks to be initiated from a formula in one of the columns. I can't figure out how to do the same thing here.
Reply With Quote
  #4  
Old 09-30-2020, 11:09 PM
Purfleet Purfleet is offline Forumla to find all words over three characters Windows 10 Forumla to find all words over three characters Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

paste the text in to column A - 1 row for each peice of text, then select however many you want to split and hit the button.

Each word over 3 charecters with no numbers will then appear in the columns to the right

I have deleted the output and reattached - to test it just select A1 to A16 and click the button

Last edited by Purfleet; 09-30-2020 at 11:10 PM. Reason: typo
Reply With Quote
  #5  
Old 10-01-2020, 09:54 AM
14spar15 14spar15 is offline Forumla to find all words over three characters Windows 7 64bit Forumla to find all words over three characters Office 2010 64bit
Advanced Beginner
Forumla to find all words over three characters
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Thanks again. What you are presenting here does what I need to do. The thing is I’m trying to do it without a button so that it’ll work just as a formula would. When data is put into a cell the results are automatically displayed. This will be just one part of a long sequence. It won’t be the beginning or it won’t be the end of the sequence just one part of the sequence. When completed I want to paste a string in Column A and get the desired results in column T without having to interact with the process. Thanks
Reply With Quote
  #6  
Old 10-01-2020, 12:52 PM
Purfleet Purfleet is offline Forumla to find all words over three characters Windows 10 Forumla to find all words over three characters Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I have never created a UDF before, so test this out =SplitIT
Reply With Quote
  #7  
Old 10-01-2020, 05:44 PM
14spar15 14spar15 is offline Forumla to find all words over three characters Windows 7 64bit Forumla to find all words over three characters Office 2010 64bit
Advanced Beginner
Forumla to find all words over three characters
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Great!! That =SplitIT() didi it. I also like the way you combined the results into on cell on the second one. That saved me a lot of trial and error. Thanks go much for the patients with me and all the help.
Reply With Quote
  #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: 3,932
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
  #9  
Old 10-02-2020, 09:58 AM
14spar15 14spar15 is offline Forumla to find all words over three characters Windows 7 64bit Forumla to find all words over three characters Office 2010 64bit
Advanced Beginner
Forumla to find all words over three characters
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

Thank you Guessed, That may be helpful in the next portion of this project.
Reply With Quote
Reply

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 10:24 AM.


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