If a cell contains a string, return a value from an array

[topi1]

In the following great formula/example from @Eric W, is it possible to change the formula in B, where it will give results even if not separated by whitespace and delimiters. It will help if it at least ignores delimiters like ".", "?" and "!". Thank you.

Book1
	A	B	C
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters	Yellow	Yellow
31	xxgreenxx if the string needs to be isolated by whitespace or delimiters	no match	Green
32	Red	Red
33	Blue	Blue
34	Green	Green
35	Yellow	Yellow
Sheet1

Cell Formulas
Range	Formula
B28:B35	B28	=IFERROR(LOOKUP(2,1/SEARCH(" "&$C$28:$C$35&" "," "&A2&" "),$C$28:$C$35),"no match")

 

[topi1]

Cheers.

Two-word (or three-words) keys will be challenging I suppose. I don't anticipate it and if I encounter it, I will try to find a way to insert "_" (underscore) and make a single word. May be consider fuzzyvlookup. Trying to bite more than I can chew! Thank you everyone here for help.

 

[Peter_SSs]

Two-word (or three-words) keys will be challenging I suppose.

The udf should be able to be modified for that if required. If it crops up then you can always post back with more details and examples to see what can be done.

 

[topi1]

@Peter_SSs What I am requesting is not urgent and is probably of diminishing returns if it requires great deal of time and effort but if you build it, I will definitely use it. Here is the example. The output with the current system gives results as listed in the column B. I would like the results as listed in the column C.
Thank you.

rscripto22.xlsm
	A	B	C	D	E
22	Sample	Output	Desired Output		Key
23	Right front tire needs replacement	Right	Right front tire		Right front tire
24	Right wiper is broken	Right	wiper		wiper
25	Front mirror is new.	Front	front mirror		front mirror
Extract_FM

Cell Formulas
Range	Formula
B23:B25	B23	=GetKey(A23,E$2:E$1000)

 

[Peter_SSs]

Try this one

Function Get_Key(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  Dim sPat As String

  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = bIgnoreCase
  a = rKeys.Value
  For i = 1 To UBound(a)
    If Len(a(i, 1)) > 0 Then sPat = sPat & "|" & a(i, 1)
  Next i
  RX.Pattern = "\b(" & Mid(sPat, 2) & ")\b"
  Get_Key = "no match" '<- Edit to suit
  If RX.Test(s) Then Get_Key = RX.Execute(s)(0)
End Function

topi1_2.xlsm
	A	B	C	D	E
22	Sample	Output	Desired Output		Key
23	Right front tire needs replacement	Right front tire	Right front tire		Right front tire
24	Right wiper is broken	wiper	wiper		wiper
25	Front mirror is new.	Front mirror	front mirror		front mirror
26	Left wheel	no match
Sheet5

Cell Formulas
Range	Formula
B23:B26	B23	=Get_Key(A23,E$2:E$1000)

 

[topi1]

Try this one

Function Get_Key(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  Dim sPat As String

  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = bIgnoreCase
  a = rKeys.Value
  For i = 1 To UBound(a)
    If Len(a(i, 1)) > 0 Then sPat = sPat & "|" & a(i, 1)
  Next i
  RX.Pattern = "\b(" & Mid(sPat, 2) & ")\b"
  Get_Key = "no match" '<- Edit to suit
  If RX.Test(s) Then Get_Key = RX.Execute(s)(0)
End Function

topi1_2.xlsm
	A	B	C	D	E
22	Sample	Output	Desired Output		Key
23	Right front tire needs replacement	Right front tire	Right front tire		Right front tire
24	Right wiper is broken	wiper	wiper		wiper
25	Front mirror is new.	Front mirror	front mirror		front mirror
26	Left wheel	no match
Sheet5

Cell Formulas
Range	Formula
B23:B26	B23	=Get_Key(A23,E$2:E$1000)

Amazing!! Thank you so much! Greatly appreciate it!

 

[Peter_SSs]

Cheers. Glad to help.

 

[topi1]

Cheers. Glad to help.

@Peter_SSs Just playing around. The following vba works in matching A with E as long as E has only single word. Can it be modified to achieve results like above and match multi-word string in E? Thank you. Only time permitting since we already have a solution. Full disclosure. Just learned about the search engine perplexity and I asked it to give a vba for this thread and this is what it gave. Crazy, AI spitting out replies like that. But you still trump as it could not come up with the answer for multi-word match! Thank you. Only if it is a quick browse and quick modification. TY.

Sub ExtractFirstMatchingWordModified()
    Dim cell As Range
    Dim searchRange As Range
    Dim matchRange As Range
    Dim words As Variant
    Dim found As Boolean
    Dim i As Integer
    Dim regex As Object
    Dim inputString As String

    ' Create a new Regex object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .IgnoreCase = True
        .Pattern = "[,./?!-!]+" ' Regex pattern to match the specified delimiters
    End With

    ' Set the range to search for the first string
    Set searchRange = Range("A1:A100") ' Assuming the data is in A1:A100

    ' Loop through each cell in the search range
    For Each cell In searchRange
        ' Use Regex to replace delimiters with a space in the cell's value
        inputString = regex.Replace(cell.Value, " ")
       
        ' Split the modified string into words
        words = Split(inputString, " ")
        found = False

        ' Loop through each word
        For i = LBound(words) To UBound(words)
            ' Find the first matching word in the array E1:E100
            Set matchRange = Range("E1:E100").Find(What:=words(i), LookIn:=xlValues, LookAt:=xlWhole)

            ' If a match is found, place the word in column B and exit the loop
            If Not matchRange Is Nothing Then
                cell.Offset(0, 1).Value = words(i)
                found = True
                Exit For
            End If
        Next i

        ' If no match is found, leave the adjacent cell in column B empty
        If Not found Then
            cell.Offset(0, 1).Value = ""
        End If
    Next cell
End Sub

 

[Peter_SSs]

Sorry, not interested in trying to work out what AI is trying to do (often incorrectly and/or inefficiently)

 

[topi1]

Sorry, not interested in trying to work out what AI is trying to do (often incorrectly and/or inefficiently)

@Peter_SSs I completely understand and respect that. I appreciate you replying to my request. And of course, always appreciate tremendous help from you and others. Thank you.