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

     

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

    • githinmg99's avatar
      githinmg99
      Copper Contributor
      Jul 17, 2022
      Thanks Lorenzo 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?
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        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))
)

         

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 16, 2022

    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.

Resources