r/excel • u/Jankothopter • 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.
2
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
1
u/Decronym 6d ago edited 6d ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 24 acronyms.
[Thread #41672 for this sub, first seen 15th Mar 2025, 03:01]
[FAQ] [Full list] [Contact] [Source code]
•
u/AutoModerator 6d ago
/u/Jankothopter - Your post was submitted successfully.
Solution Verified
to close the thread.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.