To compare two delimited text strings and extract common values, you can use the TEXTJOIN, TRUE, FILTER, and COUNTIF functions.
How to extract common values from text strings
To extract common values from text strings in Excel, follow these steps:
- Open Excel.
- Type =TEXTJOIN(“, “, TRUE, FILTER(TEXTSPLIT(A2, ” “), COUNTIF(B2, “” & TEXTSPLIT(A2, ” “) & “”)))
- Press Enter.
- This formula returns the common values in both strings, separated by commas.
Example
In the example, we have two cells with comma-separated words. The goal is to create a formula that extracts specific words from C2 based on a condition defined in C2 and then combines them into a single, comma-separated string.
Apply the following formula for C2 and C4:
=TEXTJOIN(“, “, TRUE, FILTER(TEXTSPLIT(TRIM(C2), “, “), COUNTIF(C4, “”&TRIM(TEXTSPLIT(C2, “, “))&””)))
The result looks great:
What if you have multiple spaces or commas?
The following formula is designed to extract common values between two comma-separated lists (in cells C2 and C4) and then display these shared values in a single, cleanly formatted text without any extra commas.
Formula:
=SUBSTITUTE(TEXTJOIN(“, “, TRUE, FILTER(TEXTSPLIT(TRIM(C2), “, “), COUNTIF(C4, “”&TRIM(TEXTSPLIT(C2, “, “))&””))), “, ,”, “,”)
Explanation
Evaluate the formula from the inside out:
- TRIM(C2): Removes any leading or trailing spaces from the text in C2.
- TEXTSPLIT(TRIM(C2), “, “): Splits the trimmed text from C2 into an array based on commas. A comma separates each part of C2 and becomes a separate item.
- COUNTIF(C4, “*”&TRIM(TEXTSPLIT(C2, “, “))&”*”): Creates a condition to filter only those items from C2 that match the pattern in C4. This uses wildcards to check if any part of C2 exists within C4.
- FILTER(…, COUNTIF(…)): Uses the filter condition to extract only the items from C2 that meet the criteria set by C4. This results in an array of matching items.
- TEXTJOIN(“, “, TRUE, FILTER(…)): This function joins the filtered items into a single text string, separated by commas, skipping any blank cells.
- SUBSTITUTE(…, “, ,”, “,”): Cleans up any extra commas that may appear in the resulting text due to blank values.