How to "match" or "search" multiple/partial text values out of sequential order and still work

[wbstadeli]

Hi guys,

Is there any way to search a cell for text words that the words do not have to be in order? I can get this formula to work using if everything i type is in the exact order, but not if i try to switch some words around.

Formula using search: =isnumber(search(trim(C1),A1))

See example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]the dog runs fast[/TD]
[TD][/TD]
[TD]dog runs[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This returns a "True", however see next example:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]the dog runs fast[/TD]
[TD][/TD]
[TD]runs dog[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This returns "False".

Anyway to get this to be true? Im open to helper columns if needed as well. Thanks for looking!

 

[yky]

How many words would be in C? If more than two, you may want to try VBA.

You can use the following two string functions to get "runs" and "dog".

=LEFT(C1,FIND(" ",C1)) <- for "runs"
=RIGHT(C1,LEN(C1)-FIND("dog",C1)+1) <- for "dog"

=IF(AND(ISNUMBER(SEARCH(LEFT(C1,FIND(" ",C1)),A1)),ISNUMBER(SEARCH(RIGHT(C1,LEN(C1)-FIND("dog",C1)+1),A1))),"TRUE","FALSE")

 

[wbstadeli]

How many words would be in C? If more than two, you may want to try VBA.

You can use the following two string functions to get "runs" and "dog".

=LEFT(C1,FIND(" ",C1)) <- for "runs"
=RIGHT(C1,LEN(C1)-FIND("dog",C1)+1) <- for "dog"

=IF(AND(ISNUMBER(SEARCH(LEFT(C1,FIND(" ",C1)),A1)),ISNUMBER(SEARCH(RIGHT(C1,LEN(C1)-FIND("dog",C1)+1),A1))),"TRUE","FALSE")

Thanks yky, i apologize i wasn't very clear with my post. The words/ numbers would be variable, so i cant have anything the finds the actual word (example; "dog" or "runs", rather "anyword" or "anynumber"), if that makes sense. Thanks for your reply though!

 

[yky]

You can substitute "dog" with the first formula to get the following:

=IF(AND(ISNUMBER(SEARCH(LEFT(C1,FIND(" ",C1)),A1)),ISNUMBER(SEARCH(RIGHT(C1,LEN(C1)-FIND(LEFT(C1,FIND(" ",C1)),C1)+1),A1))),"TRUE","FALSE")

As you can see, with only two words, the formula is already so long. With more than two words, you need to find other solutions such as VBA.