#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
I think a formula would be very messy and long, how about a bit of vba?
|
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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
|
#6
|
|||
|
|||
I have never created a UDF before, so test this out =SplitIT
|
#7
|
|||
|
|||
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.
|
#8
|
||||
|
||||
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 =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 |
#9
|
|||
|
|||
Thank you Guessed, That may be helpful in the next portion of this project.
|
|
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 |