Forum Discussion

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

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

SAMPLE_githinmg99.xlsx20 KB

Lorenzo

Silver Contributor

Jul 18, 2022

githinmg99

I'm at lost. In your reply to HansVogelaar 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

githinmg99

Copper Contributor

Jul 18, 2022

Lorenzo 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,";"))),""))
)

Lorenzo
Silver Contributor
Jul 19, 2022
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)
test wb.xlsx11 KB