Extract common values from text strings

Extract common values from text strings

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:

  1. Open Excel.
  2. Type =TEXTJOIN(“, “, TRUE, FILTER(TEXTSPLIT(A2, ” “), COUNTIF(B2, “” & TEXTSPLIT(A2, ” “) & “”)))
  3. Press Enter.
  4. This formula returns the common values in both strings, separated by commas.

Example

In this example, we have two text strings in cells C2 and C4 representing lists of fruits separated by commas. We want to to identify and extract the common items in both lists. The formula in cell C7 uses the TEXTSPLIT function to break each string into individual items and the FILTER function to extract the overlapping items by comparing the two lists. The common items are then joined into a single string using the TEXTJOIN function, providing a concise summary of the shared values.

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:

  1. TRIM(C2): Removes any leading or trailing spaces from the text in C2.
  2. 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.
  3. 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.
  4. 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.
  5. TEXTJOIN(“, “, TRUE, FILTER(…)): This function joins the filtered items into a single text string, separated by commas, skipping any blank cells.
  6. SUBSTITUTE(…, “, ,”, “,”): Cleans up any extra commas that may appear in the resulting text due to blank values.

Download the practice file!