Data Cleaning & Text
Extract Common Shared Items Between Two Comma-Delimited Text Strings
Tokenize two comma-separated tag or skill strings with TEXTSPLIT and TOCOL, filter the overlapping tokens, and return a clean comma-joined intersection.
Compare delimited tag lists accurately without substring false positives
Comparing raw strings with SEARCH("SQL", B3) falsely matches "NoSQL" or "MySQL"! Splitting both strings into discrete token arrays with TOCOL(TEXTSPLIT(cell, ", ")) compares complete tokens rather than substrings.
Comparing candidate tokens from List A against the tokens of List B with elementwise equality inside FILTER and re-joining matches with TEXTJOIN(", ", TRUE, ...) outputs the exact shared skill or permission set.
| Input Cell | Delimited Token String | Discrete Tokens | Shared Intersection |
|---|---|---|---|
| Role Requirements (B2) | SQL, Python, BI, Finance | 4 tokens | Python, Finance |
| Candidate Profile (B3) | Finance, Rust, Python, Cloud | 4 tokens | 2 shared skills |
Shared Token Intersection Between Two 4-Tag Strings
Splits B2 and B3 on ", ", filters tokens in B2 that exactly equal any token in B3, and joins them.
=LET(reqList, TOCOL(TEXTSPLIT(B2, ", ")), candList, TOCOL(TEXTSPLIT(B3, ", ")), matchMask, (reqList = INDEX(candList, 1)) + (reqList = INDEX(candList, 2)) + (reqList = INDEX(candList, 3)) + (reqList = INDEX(candList, 4)) > 0, TEXTJOIN(", ", TRUE, FILTER(reqList, matchMask, "No Overlap")))Shared Token Overlap Count
Counts how many required tags from B2 are present as exact tokens in B3.
=LET(reqList, TOCOL(TEXTSPLIT(B2, ", ")), candList, TOCOL(TEXTSPLIT(B3, ", ")), SUM(--((reqList = INDEX(candList, 1)) + (reqList = INDEX(candList, 2)) + (reqList = INDEX(candList, 3)) + (reqList = INDEX(candList, 4)) > 0)))Try the example
Use the original values or change them to test your understanding.