Return cells only containing certain letters

[oaj1977]

Here’s my scenario:

I have a list of words and a list of letters.
I want to return the words that contain letters from the list of letters and ONLY from the list of letters. For example:

WORDS: cog, bib, ab, back, cab
LETTERS: A, B, C
WORDS returned: ab, cab

Thoughts?

 

[Rick Rothstein]

What if there are repeated letters? For example, say your letters are B, C, I... would the word BIB be returned to you or not?

 

[JoeMo]

Assuming the answer to Rick's question is yes, here's a UDF you can try. Assumes the list of words and the list of allowed letters are ", "-delimited strings and you don't want to employ case sensitivity.

Book1
	A	B	C	D
1	cog, bib, ab, back, cab	ab, cab		Allowed Letters
2	bib, ibb, jkl, mno			A, B, C
3	aCB, BBB, BBA, aab, ccc	aCB, BBB, BBA, aab, ccc
4
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=oaj(A1,$D$2)

Function oaj(Words As String, Allowed As String) As String
'Assumes delimiter is always ", " for both Words and Allowed
Dim Vwrds As Variant, Vallow As Variant, i As Long, j As Long, k As Long, Ct As Long, Out As Variant
Vwrds = Split(Words, ", ")
Vallow = Split(Allowed, ", ")
For i = LBound(Vwrds) To UBound(Vwrds)
    Ct = 0
    For j = 1 To Len(Vwrds(i))
        For k = LBound(Vallow) To UBound(Vallow)
            If UCase(Mid(Vwrds(i), j, 1)) = UCase(Vallow(k)) Then
                Ct = Ct + 1
                If Ct = Len(Vwrds(i)) Then
                    Out = Out & ", " & Vwrds(i)
                    GoTo Nx
                End If
            End If
        Next k
    Next j
Nx:
Next i
If Out <> "" Then
    oaj = Mid(Out, 3, Len(Out))
Else
    oaj = ""
End If
End Function

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is an alternative user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function myWords(s As String, letters As String) As String
  Dim RX As Object, itm As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "(\b[" & letters & "]+\b)"
  For Each itm In RX.Execute(s)
    myWords = myWords & IIf(myWords = vbNullString, "", ", ") & itm
  Next itm
End Function

oaj1977.xlsm
	A	B	C	D
1	Words	Results		Allowed Letters
2	cog, bib, ab, back, cab	ab, cab		ABC
3	bib, ibb, jkl, mno
4	aCB, BBB, BBA, aab, ccc	aCB, BBB, BBA, aab, ccc
5
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=myWords(A2,D$2)

 

[oaj1977]

What if there are repeated letters? For example, say your letters are B, C, I... would the word BIB be returned to you or not?

Yes - repeated letters would be returned.

 

[oaj1977]

What if there are repeated letters? For example, say your letters are B, C, I... would the word BIB be returned to you or not?

Yes - repeated letters would be returned.

Welcome to the MrExcel board!

Here is an alternative user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function myWords(s As String, letters As String) As String
  Dim RX As Object, itm As Object

  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "(\b[" & letters & "]+\b)"
  For Each itm In RX.Execute(s)
    myWords = myWords & IIf(myWords = vbNullString, "", ", ") & itm
  Next itm
End Function

oaj1977.xlsm
	A	B	C	D
1	Words	Results		Allowed Letters
2	cog, bib, ab, back, cab	ab, cab		ABC
3	bib, ibb, jkl, mno
4	aCB, BBB, BBA, aab, ccc	aCB, BBB, BBA, aab, ccc
5
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=myWords(A2,D$2)

so the list of words will be in individual cells and be on the magnitude of tens of thousands.

 

[oaj1977]

Assuming the answer to Rick's question is yes, here's a UDF you can try. Assumes the list of words and the list of allowed letters are ", "-delimited strings and you don't want to employ case sensitivity.

Book1
	A	B	C	D
1	cog, bib, ab, back, cab	ab, cab		Allowed Letters
2	bib, ibb, jkl, mno			A, B, C
3	aCB, BBB, BBA, aab, ccc	aCB, BBB, BBA, aab, ccc
4
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=oaj(A1,$D$2)

Function oaj(Words As String, Allowed As String) As String
'Assumes delimiter is always ", " for both Words and Allowed
Dim Vwrds As Variant, Vallow As Variant, i As Long, j As Long, k As Long, Ct As Long, Out As Variant
Vwrds = Split(Words, ", ")
Vallow = Split(Allowed, ", ")
For i = LBound(Vwrds) To UBound(Vwrds)
    Ct = 0
    For j = 1 To Len(Vwrds(i))
        For k = LBound(Vallow) To UBound(Vallow)
            If UCase(Mid(Vwrds(i), j, 1)) = UCase(Vallow(k)) Then
                Ct = Ct + 1
                If Ct = Len(Vwrds(i)) Then
                    Out = Out & ", " & Vwrds(i)
                    GoTo Nx
                End If
            End If
        Next k
    Next j
Nx:
Next i
If Out <> "" Then
    oaj = Mid(Out, 3, Len(Out))
Else
    oaj = ""
End If
End Function

The list of words will be in individual cells and will number in the tens of thousands. :-/

 

[JoeMo]

The list of words will be in individual cells and will number in the tens of thousands. :-/

Please post a sample of your data, using XL2BB, both the list of words and the list of allowed letters so we can see the layout.

 

[oaj1977]

I've been getting some compile errors with XL2BB, so here it is as a table and also attached a screenshot...

Letters:	g
	i
	e
	l
	a
	d
Words
aahs
aals
aani
aaru
abac
abay
abas
abba
abbe
abby
abbr
abed
abey
abel
abet
abib
abie
abye
abir
abys
abit
able
ably
abos
abow
abox
abri
absi
abut
acad
acca
acce
acct
aced
acer
aces
ache
achy
acid
acyl
acis
acle
aclu
acme
acne
acop
acor
acpt
acre
acta
actg
acts
actu
acus
adad
adai
aday
adam
adar
adat
adaw
adda
addy
addn
addr
adds
addu
aden
adet
adib
adin
adit
adjt
admi
adod
adon
ador
ados
adry
advt
adze
aeon
aery
aero
aesc
afar
afer
affa
affy
afft
afro
agad
agag
agal
agao
agar
agas
agau
agaz
agba
agcy
aged
agee
agen
ager
ages
aget
agha
agib
agin
agio
agit
agla
agly
agma
agog
agon
agos
agra
agre
agst
agua
ague
ahab
ahey
ahem
ahet
ahir
ahoy
ahom
ahum
ayah
aias
aide
aids
aiel
ayen
ayes
ayin

 

[JoeMo]

See if this does what you want:

Book1.xlsm
	A	B
1	Letters:	g
2		i
3		e
4		l
5		a
6		d
7
8
9	Words
10	aahs
11	aals
12	adad	adad
13	adai	adai
14	ADAI	ADAI
15	aDAi	aDAi
16	abas
17	abba
18	abbe
19	abby
20	abbr
Sheet3

Cell Formulas
Range	Formula
B10:B20	B10	=Allow(A10,$B$1:$B$6)

Function Allow(Words As String, Lett As Variant) As String
Dim Vallow As Variant, i As Long, j As Long, Ct As Long
Vallow = Lett.Value
For i = 1 To Len(Words)
    For j = LBound(Vallow, 1) To UBound(Vallow, 1)
        If UCase(Mid(Words, i, 1)) = UCase(Vallow(j, 1)) Then
            Ct = Ct + 1
            If Ct = Len(Words) Then
                Allow = Words
                GoTo Nx
            End If
        End If
    Next j
    Allow = ""
Nx:
Next i
End Function