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 multip...
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