Formula to edit part of a text in one cell based on an values in another cell

[githinmg99]

Hi guys, I'm looking for a formula for one of my projects which can help in removing certain parts of a cell (Cell A - names separated by a ;) based on the values in Cell B (similar format as Cell A).

Example 

Cell A - cat;dog;rat;rabbit

Cell B - rabbit;dog

Result needs to be - cat;rat 

[#2]

@githinmg99 

Here is a custom VBA function:

Function Subtract(s1 As String, s2 As String) As String
    Dim a1() As String, a2() As String
    Dim v As Variant
    a1 = Split(s1, ";")
    a2 = Split(s2, ";")
    For Each v In a2
        a1 = Filter(a1, v, False)
    Next v
    Subtract = Join(a1, ";")
End Function

Use like this:

=Subtract(CellA,CellB)

[#3]

@Hans Vogelaar thank you so much for your input on this and this works perfectly fine. Although I'm running a cloud flow using power automate on this excel and don't want it to affect by using VBA as my connector is excel online. Is there any way I could achieve this same result without using VBA?

[#4]

@githinmg99 

It might be possible using the new string manipulation functions such as TEXTSPLIT but I don't have those yet. Let's see if someone else comes up with a formula-based solution.

[#5]

Hi @githinmg99 

With Excel Online you can't use FILTERXML to split A2/B2. Until TEXTSPLIT is released, define name TEXT_SPLIT with formula:

=LAMBDA(value,delim,
    LET(
        seq,       SEQUENCE(LEN(value)+1),
        Positions, LAMBDA(string,d,s,
            LET(
                split, MID(string,s,1),
                arr,   IF(split = d, s),
                FILTER(arr, ISNUMBER(arr))
            )
        ),
        Start,     Positions(delim & value, delim, seq),
        End,       Positions(value & delim, delim, seq),
        MID(value, Start, End-Start)
    )
)

then in D2:

=LET(
    value, TEXT_SPLIT(A2,";"),
    TEXTJOIN(";",,FILTER(value,ISNA(XMATCH(value,TEXT_SPLIT(B2,";")))))
)

When TEXTSPLIT is released

- Replace TEXT_SPLIT with TEXTSPLIT in the above formula

- Delete TEXT_SPLIT in the Named Manager

[#6]

@githinmg99 

For the future:

=TEXTJOIN(";",,UNIQUE(TEXTSPLIT(TEXTJOIN(";",,A1:B1),";"),1,1))

[#7]

Thanks @L z. I'm getting a #NAME? error while applying this. I've doubled checked my TEXT_SPLIT formula in name manager and formula in cell. Any idea why this is happening?

[#8]

@githinmg99 

A #NAME? error means what it means :) - one or more of your defined name or variable isn't recognized

Difficult to say more without seeing your formulas. Could you share your test workbook with i.e OneDrive, Google Drive... ?

[#9]

@githinmg99  With Microsoft 365 you can use below formula-

    =TEXTJOIN(";",TRUE,LET(x,FILTERXML("<t><s>"&SUBSTITUTE(A2,";","</s><s>")&"</s></t>","//s"),y,FILTERXML("<t><s>"&SUBSTITUTE(B2,";","</s><s>")&"</s></t>","//s"),FILTER(x,NOT(ISNUMBER(XMATCH(x,y,0))))))

[#10 · Accepted solution]

@githinmg99 

In attached file, a different approach for TEXT_SPLIT:

=LAMBDA(Value,Delimiter,
    LET(
        Positions, LAMBDA(String,Delim,Sequence,
            LET(
                k, SCAN(0,Sequence, LAMBDA(acc,s, IF(MID(String,s,1)=Delim, acc+s, 0))),
                FILTER(k, k > 0)
            )
        ),
        Seq,   SEQUENCE(LEN(Value)+1),
        Start, Positions(Delimiter & Value, Delimiter, Seq),
        End,   Positions(Value & Delimiter, Delimiter, Seq),
        MID(Value, Start, End-Start)
    )
)

Updated previous Result formula to account for nothing to return and added a couple of other options that avoid spliting A2 (better IMHO)

in D2:

=LET(
    value, TEXT_SPLIT(A2,";"),
    TEXTJOIN(";",,FILTER(value,ISNA(XMATCH(value,TEXT_SPLIT(B2,";"))),""))
)

in F2:

=LET(
    Removed, REDUCE(A2&";",TEXT_SPLIT(B2,";"),
                LAMBDA(value,arr, SUBSTITUTE(value,arr&";",""))
    ),
    IF(LEN(Removed) = 0, "", LEFT(Removed, LEN(Removed)-1))
)

in H2:

=LET(
    Removed, REDUCE(LOWER(A2)&";",TEXT_SPLIT(LOWER(B2),";"),
                LAMBDA(value,arr, SUBSTITUTE(value,arr&";",""))
    ),
    IF(LEN(Removed) = 0, "", LEFT(Removed, LEN(Removed)-1))
)

[#11]

@L z. thanks again. My TEXT_SPLIT formula is still not getting recognized and is giving me the #NAME? error. I have copied the same formula to my name manager. Did I miss out anything?

[#12]

@githinmg99 

On which Excel version you are ? Perhaps SCAN() is not available. Alternatively

textSplit_ =
LAMBDA( str, sep,
    LET(
        n, LEN(str) - LEN(SUBSTITUTE(str,sep, "") ) + 1,
        fakeSep, UNICHAR(9999),
        strWithFakeSep, SUBSTITUTE(str, sep, fakeSep, n-1),
        fakeSepPosition, FIND( fakeSep, strWithFakeSep ),
        head, LEFT( strWithFakeSep, fakeSepPosition - 1 ),
        tail, RIGHT(strWithFakeSep, LEN(strWithFakeSep) - fakeSepPosition  ),
        IF( n=1, IF(str = "", "", str),
        IF( SEQUENCE(,n) < n ,
            textSplit_( head , sep ),
            tail )
        ) 
    ))

[#13]

@githinmg99 

I'm at lost. In your reply to @Hans Vogelaar  you said you were working with Excel Online where LAMBDA, SCAN, REDUCE... functions are available

The 1st version of TEXT_SPLIT I shared did not involve SCAN and you already reported a #NAME? error

IMHO it's more than time to clarify the exact version you're working with - Thanks

[#14]

@L z. @Sergei Baklan 

I'm using excel online for this project (linked to my flows in power automate) and like you mentioned LAMBDA function is available in excel online. 

Since I'm unable to define name using excel online, I'm using the desktop version (MS 365 ver 16) to just add in the LAMBDA formula under name manager where I'm getting the error. Could you please guide me on the right approach here?

[#15]

@L z. I think I can combine both your formula to create a single longer one which would work without errors. Not sure if this is an efficient way but kind of works! Thanks a lot! 

LET(
    value, LAMBDA(Value,Delimiter,
    LET(
        Positions, LAMBDA(String,Delim,Sequence,
            LET(
                k, SCAN(0,Sequence, LAMBDA(acc,s, IF(MID(String,s,1)=Delim, acc+s, 0))),
                FILTER(k, k > 0)
            )
        ),
        Seq,   SEQUENCE(LEN(Value)+1),
        Start, Positions(Delimiter & Value, Delimiter, Seq),
        End,   Positions(Value & Delimiter, Delimiter, Seq),
        MID(Value, Start, End-Start)
    )
)(A2,";"),
    TEXTJOIN(";",,FILTER(value,ISNA(XMATCH(value,LAMBDA(Value,Delimiter,
    LET(
        Positions, LAMBDA(String,Delim,Sequence,
            LET(
                k, SCAN(0,Sequence, LAMBDA(acc,s, IF(MID(String,s,1)=Delim, acc+s, 0))),
                FILTER(k, k > 0)
            )
        ),
        Seq,   SEQUENCE(LEN(Value)+1),
        Start, Positions(Delimiter & Value, Delimiter, Seq),
        End,   Positions(Value & Delimiter, Delimiter, Seq),
        MID(Value, Start, End-Start)
    )
)(B2,";"))),""))
)

[#16]

@githinmg99 

Just to check...

Entered the following in Excel desktop before loading the file to OneDrive
C1: =TEXT_SPLIT(A1,",")
D1: =SPLITTEXT(A1, ",")
F1: =textSplit_(A1,", ")

Edited the file in Excel online and added:
C5: =TEXT_SPLIT(A1,",")
D5: =SPLITTEXT(A1, ",")
F2: =textSplit_(A1,", ")

Can't repro. the #NAME? error - Sorry but no idea right now

[#17]

@githinmg99 

Thanks for sharing a sample workbook with the #NAME? error (attached here in case someone can find a reasonable explaination as I don't)

Opened your file (#NAME? in C2) > Evaluated the formula:

Though it could be a corrupted workbook or something similar. In 2 steps Copied/Pasted the name TEXT_SPLIT then the lambda formula from your workbook to a new workbook ==> No issue

Went back to your workbook, tried in a new cell =TEXT_SPLIT, pressed <Tab> ==> No auto. opening parenthese showing the function parameters (???)

Still from the workbook you shared:

- Renamed TEXT_SPLIT as HELLO ==> No more #NAME? error

- Renamed HELLO as TEXT_SPLIT ==> Still no error

(Windows Defender didn't report any issue on your file)

[#18]

@githinmg99 

As a comment, it's not necessary to jump between Excel online and desktop versions. You may install Advanced Formula Environment add-in on Excel for web and work with it.