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")

 

[Peter_SSs]

For the udf to return only the first word found left to right in col A, remove this line or change it from True to False.

RX.Global = True

 

[topi1]

@Peter_SSs Made it work. Reviewing. B looks promising. It looks like it reads A from left to right. Need to strip the first word out. Will work on it and follow up. ty.

@Peter_SSs and @DanteAmor, you guys are expert at this and you would laugh at the following, but I converted B to H going step wise from E to H. Collective formula in H.

As a final result

i) it always gives the first key found in the string regardless of its position in C.
ii) Gives when there is not match. I may change that at some point.
iii) Removes ,./?!-_

Thank you guys.
Can't wait to modify Peter's vba and retry but meanwhile wanted to share this crazy derivation.
Thank you for listening.

Book2
	A	B	C	D	E	F	G	H
27	Sample	Output	Key		Removes "no match"	"Strips first word"	"Removes ,./?-_"	FINAL
28	Red wine on the blue carpet	Red, blue	Red		Red, blue	Red,	Red	Red
29	Blue -like the color or the mood	Blue	Blue		Blue	Blue	Blue	Blue
30	yellow -testing if capitalization matters or red	yellow, red	Yellow		yellow, red	yellow,	yellow	yellow
31	xx green! if the string needs to be isolated by whitespace or delimiters	green	Green		green	green	green	green
32	Red? & blue	Red, blue			Red, blue	Red,	Red	Red
33	Blue & red	Blue, red			Blue, red	Blue,	Blue	Blue
34	Green	Green			Green	Green	Green	Green
35	yellow	yellow			yellow	yellow	yellow	yellow
36	Yellow.	Yellow			Yellow	Yellow	Yellow	Yellow
37	Tom is unpredicatble	no match
38	Blue!, green & Red has several keys	Blue, green, Red			Blue, green, Red	Blue,	Blue	Blue
Sheet1

Cell Formulas
Range	Formula
E28:E38	E28	=IF((CheckKeys(A28,C$28:C$31)="no match"),"",CheckKeys(A28,C$28:C$31))
F28:F38	F28	=IFERROR(LEFT(E28, FIND(" ", E28)-1), E28)
G28:G38	G28	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F28,",",""),"-",""),".",""),"!",""),"?",""),"_",""),"/","")
H28:H38	H28	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IFERROR(LEFT(IF((CheckKeys(A28,C$28:C$31)="no match"),"",CheckKeys(A28,C$28:C$31)), FIND(" ", IF((CheckKeys(A28,C$28:C$31)="no match"),"",CheckKeys(A28,C$28:C$31)))-1), IF((CheckKeys(A28,C$28:C$31)="no match"),"",CheckKeys(A28,C$28:C$31))),",",""),"-",""),".",""),"!",""),"?",""),"_",""),"/","")
B28:B38	B28	=CheckKeys(A28,C$28:C$31)

 

[topi1]

For the udf to return only the first word found left to right in col A, remove this line or change it from True to False.

RX.Global = True

@Peter_SSs changing from true to false changed the column B to what I had derived to H (not withstanding no match). Fantastic, Sir! Thank you. Why did I add those calculations?

How hard or time consuming will it be to convert K part of the formula to K:K instead of specific range. Can make sure that there are no intervening blanks but it will be nice to have a whole range lookup. I know I am being greedy. Thank you.

 

[Peter_SSs]

If you only want the first word left to right and don't want "no match" then the udf is even simpler.

Function CheckKeys(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = bIgnoreCase
  RX.Pattern = "\b(" & Join(Application.Transpose(rKeys), "|") & ")\b"
  If RX.Test(s) Then CheckKeys = RX.Execute(s)(0)
End Function

topi1_2.xlsm
	A	B	C
27	Sample	Output	Key
28	Red wine on the blue carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters or red	yellow	Yellow
31	xx green! if the string needs to be isolated by whitespace or delimiters	green	Green
32	Red? & blue	Red
33	Blue & red	Blue
34	Green	Green
35	yellow	yellow
36	Yellow.	Yellow
37	Tom is unpredicatble
38	Blue!, green & Red has several keys	Blue
Sheet4

Cell Formulas
Range	Formula
B28:B38	B28	=CheckKeys(A28,C$28:C$31)

to convert K part of the formula to K:K instead of specific range.

What is the "K part of the formula"?
In general I would recommend not using whole column References, but if you explain in detail &/or give examples of what you mean I can consider the question.

 

[topi1]

If you only want the first word left to right and don't want "no match" then the udf is even simpler.

Function CheckKeys(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = bIgnoreCase
  RX.Pattern = "\b(" & Join(Application.Transpose(rKeys), "|") & ")\b"
  If RX.Test(s) Then CheckKeys = RX.Execute(s)(0)
End Function

topi1_2.xlsm
	A	B	C
27	Sample	Output	Key
28	Red wine on the blue carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters or red	yellow	Yellow
31	xx green! if the string needs to be isolated by whitespace or delimiters	green	Green
32	Red? & blue	Red
33	Blue & red	Blue
34	Green	Green
35	yellow	yellow
36	Yellow.	Yellow
37	Tom is unpredicatble
38	Blue!, green & Red has several keys	Blue
Sheet4

Cell Formulas
Range	Formula
B28:B38	B28	=CheckKeys(A28,C$28:C$31)

What is the "K part of the formula"?
In general I would recommend not using whole column References, but if you explain in detail &/or give examples of what you mean I can consider the question.

Right now, the formula looks at C28:C31. It becomes tedious to manually change it if C28:C41 are not blank and need to be included in the lookup. I guess I would change to C28:C100 or something to make room for C column's growth. Thank you.

 

[Peter_SSs]

Try this then. It is partly based on your approach in post #12 where you extracted just the first word. From that I have assumed that the values in the Key list will all be single words. If that is not the case then more clarification/examples would be needed. Note that I have used a different function name here since we are only extracting (at most) a single key.

Function GetKey(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object

  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = bIgnoreCase
  RX.Pattern = "\b(" & Replace(Application.Trim(Join(Application.Transpose(rKeys))), " ", "|") & ")\b"
  If RX.Test(s) Then GetKey = RX.Execute(s)(0)
End Function

topi1_2.xlsm
	A	B	C
27	Sample	Output	Key
28	Red wine on the blue carpet	Red
29	Blue -like the color or the mood	Blue
30	yellow -testing if capitalization matters or red	yellow	Red
31	xx green! if the string needs to be isolated by whitespace or delimiters	green
32	Red? & blue	Red	Blue
33	Blue & red	Blue
34	Green	Green	Yellow
35	yellow	yellow	Green
36	Yellow.	Yellow
37	Tom is unpredicatble
38	Blue!, green & Red has several keys	Blue
Sheet4

Cell Formulas
Range	Formula
B28:B38	B28	=GetKey(A28,C$28:C$100)

 

[topi1]

@Peter_SSs Brilliant! Thank you so much for your continued support and changing the script. There is a chance that I may use "no match" or "others" if the results are blank in the column B. I can clearly change the formula in the column B to accomplish that, but I am curious if the vba can be modified to say "no match'. I know I am going back some. Regardless, with the change of formula to vba, I have something I was hoping for. Thank you all of you for your help.

 

[Peter_SSs]

Thank you so much for your continued support and changing the script.

You're welcome.

There is a chance that I may use "no match" or "others" if the results are blank ....

Add the extra line shown below and edit the text if you want to whatever result you want if it cannot find any of the Keys.

Function GetKey(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object

  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = bIgnoreCase
  RX.Pattern = "\b(" & Replace(Application.Trim(Join(Application.Transpose(rKeys))), " ", "|") & ")\b"
  GetKey = "no match"
  If RX.Test(s) Then GetKey = RX.Execute(s)(0)
End Function

 

[topi1]

You're welcome.


Add the extra line shown below and edit the text if you want to whatever result you want if it cannot find any of the Keys.

Function GetKey(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object

  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = bIgnoreCase
  RX.Pattern = "\b(" & Replace(Application.Trim(Join(Application.Transpose(rKeys))), " ", "|") & ")\b"
  GetKey = "no match"
  If RX.Test(s) Then GetKey = RX.Execute(s)(0)
End Function

Genius! Worked like a charm! I'll change it to "" when I want it blank but now I know how to modify it.

 

[Peter_SSs]

Cheers.