Forum Discussion

githinmg99's avatar
githinmg99
Copper Contributor
Jul 15, 2022
Solved

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

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 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jul 17, 2022

    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))
)

     

17 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jul 17, 2022

    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))))))

     

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 15, 2022

    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)

    • githinmg99's avatar
      githinmg99
      Copper Contributor
      Jul 16, 2022

      HansVogelaar 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?

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 16, 2022

        Hi githinmg99 

         

        With Excel Online you can't use FILTERXML to split A2/B2. Until https://support.microsoft.com/en-au/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7 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

Resources