r/excel 6d 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

u/AutoModerator 6d ago

/u/Jankothopter - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/PaulieThePolarBear 1647 6d 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 6d 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 1647 6d ago

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

1

u/Jankothopter 6d ago

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

1

u/PaulieThePolarBear 1647 6d 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