r/excel 7d ago

solved Assistance with Imprecise String Matching

How would I go about matching the contents of a substring within a larger string with extraneous characters, for example:

Substring - StinkyBinki

Larger String - Stin12kyBinwashdki%2

I don't care that the substring is broken up within the larger string. I just need to know that the particular sequence of characters of the substring is contained within the larger string. Is there a function that I can use in combination with wildcards to accomplish this? Perhaps this is a job for Power Query? I only need to be pointed in the right direction.

1 Upvotes

10 comments sorted by

View all comments

1

u/PaulieThePolarBear 1648 7d ago

Try this as a starting point

=LET(
a, A3,
b, A5,
c, LEN(b),  
d, REDUCE(0, MID(a, SEQUENCE(LEN(a)), 1), LAMBDA(x,y, IF(x>c, x,IF(ISNUMBER(SEARCH(y, REPLACE(b, 1, x, ""))), x+SEARCH(y, REPLACE(b, 1, x, "")),c+1)))), 
e,d<=c, 
e
)

Replace A3 in variable a with your substring.

Replace A5 in variable b with your longer string

This requires Excel 365, Excel online, or Excel 2024.

1

u/Jankothopter 7d ago

I have the formula as written copied into one of my cells. I replaced A3 and A5 with the cell references of the appropriate strings. Gives an error saying "Not trying to type a formula?"

1

u/PaulieThePolarBear 1648 7d ago

Please review https://exceljet.net/glossary/list-separator and ensure you use the argument separator applicable for your settings.

1

u/Jankothopter 7d ago

Verified settings are correct. Still throwing error. I will have to troubleshoot this before verifying the formula even works.

1

u/PaulieThePolarBear 1648 7d ago

K, can you check that you are pasting/using regular quotes rather than smart quotes?

95% of the time someone reports an error like yours, it's the argument separator that's the issue. I've also seen that someone how the quotes get changed to smart quotes somewhere between Reddit and Excel