• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find 4-letter words within text in a cell and store in a column

Rediska

Rediska

Member
Dear Excellians,

Long time no see.
Question: what would be the formula to find and extract 4-letter words containing characters only from a cell?
Example: from a cell texts:
a11srtd abcd dsd rt fgtd sd jkl44
dfg44 cr34 abcg we train
i need to find and extract all 4 letter-words and store them in a column, one word per cell:
abcd
fgtd
abcg

Also - I can't go with VB, but the combination of formula + macro will be acceptable
Thanks!!!
 
Last edited:
Thanks, it works - but
this is a bit lenghty - I have a large file and number of text enries in a single cell could be anywhere between 1 to 20,and formulas weight down a file size quite a bit.
I would prefer 1 column, because after these words will be extracted I'll have to compare it to a database report to find out changes/updates (magic VLOOKUP will take care of it).
 
Well, this is lengthy in other direction.

I almost see how I can narrow it down to only show needed results... but... uh, why overcomplicate? Filter and take what's yours.
 

Attachments

Thanks Xen....
but this won't work - I'll have filter through about 300 cells in 300 rows with about 20 words each.
This example was for one cell only, imagine the size of 300x20?
It's a bit complex and will go forwever....
 
Thanks Somendra,
Idea is perfect!, with one small "no" - I need 4 characters only, no numeric values within - your formula picks up "cr34" and so on.
Can it be filtered out?
 
For that then, I would recommend a macro. Select your cells and then run this macro. Should give you the list you want.
Code: 
Option Explicit
Sub MakeClean()
Dim myRange As Range
Dim myWords() As String
Dim wordCount As Integer, i As Integer, x As Integer
Dim GoodWord As Boolean
Dim c As Range
Dim wordList() As String

'How big of a word
Const wordLen = 4
'What characters to exclude
Const checkList = "1234567890"


Application.ScreenUpdating = False
wordCount = 0
'Macro will search selected range of cells
Set myRange = Selection
For Each c In myRange.Cells
    myWords = Split(c.Value, " ")
    For i = 0 To UBound(myWords)
        If Len(myWords(i)) = wordLen Then
            GoodWord = True
            For x = 1 To 4
                If InStr(1, checkList, Mid(myWords(i), x, 1)) Then
                    GoodWord = False
                End If
            Next x
            If GoodWord Then
                wordCount = wordCount + 1
                ReDim Preserve wordList(1 To wordCount)
                wordList(wordCount) = myWords(i)
            End If
        End If
    Next i
Next c

'No words found?
If wordCount <> 0 Then
ThisWorkbook.Worksheets.Add

'Populate a new blank sheet with our list
For i = 1 To wordCount
    ActiveSheet.Cells(i, 1).Value = wordList(i)
Next i
End If
Application.ScreenUpdating = True

End Sub
 
You must log in or register to reply here.
Back
Top