Forum Discussion

Carlo1825's avatar
Carlo1825
Copper Contributor
May 23, 2022

Delete common between column X and Y, from column X

Hello Excel Community! I have what I believe to be a simple request, though not simple enough for myself to have figured out. 

 

I have a spreadsheet of contacts, some of those contacts have multiple email addresses assigned to them in the column "Email Address", separated by comma. 

 

In this spreadsheet, I also have a column named "Unverified Addresses". If there are any email addresses in the "Email Address" column which are also in the "Unverified Addresses" column, I want them deleted from the "Email Address" column. I don't however want to delete the verified addresses the contacts have. So if a contact has 5 addresses, and only one is unverified, I want to keep the other four addresses. 

 

How can I do this?

 

Thank you for any help!

Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    May 24, 2022

    Carlo1825 First off, a cell formula can NOT change the value or properties of another cell, to do that you would need a macro (VBA). What I would suggest is to create a new column called Verified Email Address and then use a formula like:

    =SUBSTITUTE( [Email Address], [Unverified Addresses], "")

    that would work if there is only 1 unverified address, but it sounds like there are multiple so something like:

    =LET(   l,LEN([@email]),
   uvList,TRIM(MID(SUBSTITUTE([@unverified],",",REPT(" ",l)),SEQUENCE(l,1,1,l),l)),
    eList,TRIM(MID(SUBSTITUTE([@email],",",REPT(" ",l)),SEQUENCE(l,1,1,l),l)),
    fList,FILTER(eList,(eList<>"")*ISERROR(MATCH(eList,uvList,0)),""),
    TEXTJOIN(", ",TRUE,fList))

     and example of this in action is attached

Resources