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

     

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

       

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

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

         

         

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

      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 https://www.bing.com/ck/a?!&&p=79be330aaadd1af42346bec9f3cd9bd7459dc8ee75e0311496572fc697e814a6JmltdHM9MTY1ODA0MzQ0NSZpZ3VpZD0xNWFlNTgzNS1hZDcxLTQwY2YtODFkOC02MjUwMGMyMjg3OTQmaW5zaWQ9NTE2OQ&ptn=3&fclid=4d158797-05a3-11ed-978b-5f9950cd1121&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL3NoYXJlLW9uZWRyaXZlLWZpbGVzLWFuZC1mb2xkZXJzLTlmY2MyZjdkLWRlMGMtNGNlYy05M2IwLWE4MjAyNDgwMGMwNw&ntb=1, Google Drive... ?

Resources