Hi - Dave here.
Happy Friday!
Last week, I shared a
formula to extract common values from two ranges in Excel. This week, I want to show you how you can extract common values from two text strings.
The new formula builds directly on top of the original. We have to parse some text strings first with TEXTSPLIT, but once we do that, we can follow exactly the same approach.
You can see the results in the worksheet below. The data in row 5 contains the same values from last week. The data in row 6 shows how the formula will work with different text strings. The formula in cell D5 looks like this:
=LET(
list1,TEXTSPLIT(B5,,", "),
list2,TEXTSPLIT(C5,,", "),
common,FILTER(list1,ISNUMBER(XMATCH(list1,list2))),
TEXTJOIN(", ",1,common)
)
This formula might look scary, but it works in three basic steps (1) Split the text in cells B5 and C5 into arrays and store the results in
list1 and
list2. (2) Filter
list1 to select only shared values, and store the result in
common. (3) Join the values in
common together as text and return a final result.
[
Download the workbook and read the full explanation]
I like this example because it shows how Excel's formula language is improving. We are finally getting new tools like TEXTSPLIT, FILTER, and TEXTJOIN that make it possible to work with delimited text almost like values in individual cells. Download the workbook and try it out yourself.
Note: For now, this formula requires Excel 365, the only version that offers the TEXTSPLIT function.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
