REPLACESTRINGS

[tboulden]

REPLACESTRINGS bulk replaces matched substrings from one list with corresponding entries in a 2nd list.

If lists are not same length, only makes replacements up to the last entry in the shortest list. Function works recursively and returns the final string with all replacements.

=LAMBDA(
    searchTxt,
    findList,
    replaceList,
    LET(
        n,
        MIN(
            ROWS(findList),
            ROWS(replaceList)
        ),
        Loop,
        LAMBDA(
            ME,
            myTxt,
            i,
            LET(
                find,INDEX(findList,i),
                rep,INDEX(replaceList,i),
                new,SUBSTITUTE(myTxt,find,rep),
                IF(
                    i<n,ME(ME,new,i+1),
                    new
                )
            )
        ),
        Loop(Loop,searchTxt,1)
    )
)

LAMBDA_Testing_ReplaceStrings.xlsm
	B	C	D	E	F	G	H	I
2	Input:	This is a test
3
4	Input Lists			Modeled Behavior
5	Find	Replace		Text	i	Find	Replace	Return
6	This	That		This is a test	1	This	That	That is a test
7	is	IS		That is a test	2	is	IS	That IS a test
8	a	an		That IS a test	3	a	an	Thant IS an test
9	test	example		Thant IS an test	4	test	example	Thant IS an example
10
11	Anonymous:	Thant IS an example
12	Named:	Thant IS an example
Scratch

Cell Formulas
Range	Formula
E6	E6	=C2
G6:G9	G6	=INDEX($B$6:$B$9,F6)
H6:H9	H6	=INDEX($C$6:$C$9,F6)
I6:I9	I6	=SUBSTITUTE(E6,G6,H6)
E7:E9	E7	=I6
F7:F9	F7	=F6+1
C11	C11	=LAMBDA(searchTxt,findList,replaceList,LET(n,MIN(ROWS(findList),ROWS(replaceList)),Loop,LAMBDA(ME,myTxt,i,LET(find,INDEX(findList,i),rep,INDEX(replaceList,i),new,SUBSTITUTE(myTxt,find,rep),IF(i<n,ME(ME,new,i+1),new))),Loop(Loop,searchTxt,1)))(C2,B6:B9,C6:C9)
C12	C12	=ReplaceStrings(C2,B6:B9,C6:C9)

 

[smozgur]

Beautiful implementation of a Loop by using LET, and also using the ME concept for recursion instead of only for testing a Lambda function without creating the named range!

Thanks for sharing this.


I would like to suggest one thing unrelated to the function but providing a sample with XL2BB. Please try to start from cell A1 instead of cell B2 (or another random cell) as much as possible. Honestly, it confused me when I copied & pasted it in a worksheet since the formulas are still using the references of how they were created in the sample mini-sheet, therefore all I get is an error at the first copy & paste attempt. I mostly check the top-left cell reference when I copy a mini-sheet from the board, and it might not be a big deal starting from B2, however, it gets really hard when it starts from DQ3.

Again, beautiful implementation!

 

[tboulden]

Beautiful implementation of a Loop by using LET, and also using the ME concept for recursion instead of only for testing a Lambda function without creating the named range!

Thanks!

I would like to suggest one thing unrelated to the function but providing a sample with XL2BB. Please try to start from cell A1 instead of cell B2 (or another random cell) as much as possible. Honestly, it confused me when I copied & pasted it in a worksheet since the formulas are still using the references of how they were created in the sample mini-sheet, therefore all I get is an error at the first copy & paste attempt. I mostly check the top-left cell reference when I copy a mini-sheet from the board, and it might not be a big deal starting from B2, however, it gets really hard when it starts from DQ3.

Ahh, I see, this isn't an annoyance that had occurred to me, but I will make a point to keep close to A1 in the future!

 

[tboulden]

And with some inspiration from Dave Bruns' latest, here's a non-recursive version. Note that I think this could be LET-only if you replace the STARTPOS and LIST.COMBINE references appropriately. A notable difference is that this version requires a delimiter to split searchTxt on; this could probably be eliminated, but I think it would be more complicated than trying to do the same thing with the recursive version.

=LAMBDA(
    searchTxt,
    delimiter,
    findList,
    replaceList,
    LET(
        delPositions,StartPos(searchTxt,delimiter,"ALL"),
        splitPositions,
            List.Combine(
                List.Combine(1,delPositions),
                LEN(searchTxt)+1
            ),
        rowCt,ROWS(splitPositions),
        startPositions,INDEX(splitPositions,SEQUENCE(rowCt-1)),
        endPositions,INDEX(splitPositions,SEQUENCE(rowCt-1,1,2)),
        chunks,
            MID(
                searchTxt,
                startPositions,
                endPositions-startPositions
            ),
        subDel,SUBSTITUTE(chunks,delimiter,""),
        findIndices,MATCH(subDel,findList,0),
        finds,INDEX(findList,findIndices),
        repls,INDEX(replaceList,findIndices),
        replace,
            IF(
                ISERROR(
                    INDEX(
                        finds,
                        SEQUENCE(rowCt-1)
                    )
                ),
                subDel,
                repls
            ),
        join,TEXTJOIN(delimiter,TRUE,replace),
        join
    )
)

LAMBDA_Testing_ReplaceStrings.xlsm
	A	B	C	D	E
1	Anonymous:	The black dog sat on #@! grass
2
3
4	Text			find	replace
5	The white dog sat on green grass	The black dog sat on #@! grass		red	ugly
6	He wore a warm gray sweater	He wore a warm gray sweater		blue	123
7	The sun set red on the blue horizon	The sun set ugly on the 123 horizon		white	black
8	There were pink neon signs everywhere	There were pinkish neon signs everywhere		pink	pinkish
9	The waves where a deep blue color	The waves where a deep 123 color		green	#@!
10	She was wearing hot pink socks	She was wearing hot pinkish socks
11	Her eyes were blue and green	Her eyes were 123 and #@!
12	The basket held one green sock	The basket held one #@! sock
13	The brown belt was simply missing	The brown belt was simply missing
14	A white banner with blue and red text	A black banner with 123 and ugly text
15	Red, white, and blue	Red, white, and 123
ContainsWhichThings

Cell Formulas
Range	Formula
B1	B1	=LAMBDA(searchTxt,delimiter,findList,replaceList,LET(delPositions,StartPos(searchTxt,delimiter,"ALL"),splitPositions,List.Combine(List.Combine(1,delPositions),LEN(searchTxt)+1),rowCt,ROWS(splitPositions),startPositions,INDEX(splitPositions,SEQUENCE(rowCt-1)),endPositions,INDEX(splitPositions,SEQUENCE(rowCt-1,1,2)),chunks,MID(searchTxt,startPositions,endPositions-startPositions),subDel,SUBSTITUTE(chunks,delimiter,""),findIndices,MATCH(subDel,findList,0),finds,INDEX(findList,findIndices),repls,INDEX(replaceList,findIndices),replace,IF(ISERROR(INDEX(finds,SEQUENCE(rowCt-1))),subDel,repls),join,TEXTJOIN(delimiter,TRUE,replace),join))(A5," ",things,replace)
B5:B15	B5	=ReplaceStrings(A5," ",things,replace)

Named Ranges
Name	Refers To	Cells
replace	=ContainsWhichThings!$E$5:$E$9	B1, B5:B15
things	=ContainsWhichThings!$D$5:$D$9	B1, B5:B15

 

[tboulden]

this could probably be eliminated, but I think it would be more complicated than trying to do the same thing with the recursive version.

And I can't leave my own challenge unaccepted, here's the non-recursive version of the original.

=LAMBDA(
    searchTxt,
    findList,
    replaceList,
    LET(
        findsInTxt,FIND(findList,searchTxt),
        findBools,ISNUMBER(findsInTxt),
        initFinds,FILTER(findList,findBools,""),
        findStartPositions,FILTER(findsInTxt,NOT(ISERROR(findsInTxt)),""),
        findEndPositions,findStartPositions+LEN(initFinds),
        findSplitPositions,
            SORT(
                List.Combine(findStartPositions,findEndPositions)
            ),
        splitPositions,
            List.Combine(
                List.Combine(1,findSplitPositions),
                LEN(searchTxt)+1
            ),
        rowCt,ROWS(splitPositions),
        startPositions,INDEX(splitPositions,SEQUENCE(rowCt-1)),
        endPositions,INDEX(splitPositions,SEQUENCE(rowCt-1,1,2)),
        chunks,
            MID(
                searchTxt,
                startPositions,
                endPositions-startPositions
            ),
        findIndices,MATCH(chunks,findList,0),
        finds,INDEX(findList,findIndices),
        repls,INDEX(replaceList,findIndices),
        replace,
            IF(
                ISERROR(
                    INDEX(
                        finds,
                        SEQUENCE(rowCt-1)
                    )
                ),
                chunks,
                repls
            ),
        join,IFERROR(TEXTJOIN("",TRUE,replace),searchTxt),
        join
    )
)

LAMBDA_Testing_ReplaceStrings.xlsm
	A	B	C	D	E	F
1	Anonymous:	The black dog sat on #@! grass
2
3
4	Text	LET-based, non-recursive	Recurisve		find	replace
5	The white dog sat on green grass	The black dog sat on #@! grass	The black dog sat on #@! grass		red	ugly
6	He wore a warm gray sweater	He wore a warm gray sweater	He wore a warm gray sweater		blue	123
7	The sun set red on the blue horizon	The sun set ugly on the 123 horizon	The sun set ugly on the 123 horizon		white	black
8	There were pink neon signs everywhere	There were pinkish neon signs everywhere	There were pinkish neon signs everywhere		pink	pinkish
9	The waves where a deep blue color	The waves where a deep 123 color	The waves where a deep 123 color		green	#@!
10	She was wearing hot pink socks	She was wearing hot pinkish socks	She was wearing hot pinkish socks
11	Her eyes were blue and green	Her eyes were 123 and #@!	Her eyes were 123 and #@!
12	The basket held one green sock	The basket held one #@! sock	The basket held one #@! sock
13	The brown belt was simply missing	The brown belt was simply missing	The brown belt was simply missing
14	A white banner with blue and red text	A black banner with 123 and ugly text	A black banner with 123 and ugly text
15	Red, white, and blue	Red, black, and 123	Red, black, and 123
16
17		LET-based, non-recursive, delimiter
18		The black dog sat on #@! grass
19		He wore a warm gray sweater
20		The sun set ugly on the 123 horizon
21		There were pinkish neon signs everywhere
22		The waves where a deep 123 color
23		She was wearing hot pinkish socks
24		Her eyes were 123 and #@!
25		The basket held one #@! sock
26		The brown belt was simply missing
27		A black banner with 123 and ugly text
28		Red, white, and 123
ContainsWhichThings

Cell Formulas
Range	Formula
B1	B1	=LAMBDA(searchTxt,findList,replaceList, LET( findsInTxt,FIND(findList,searchTxt), findBools,ISNUMBER(findsInTxt), initFinds,FILTER(findList,findBools,""), findStartPositions,FILTER(findsInTxt,NOT(ISERROR(findsInTxt)),""), findEndPositions,findStartPositions+LEN(initFinds), findSplitPositions, SORT( List.Combine(findStartPositions,findEndPositions) ), splitPositions, List.Combine( List.Combine(1,findSplitPositions), LEN(searchTxt)+1 ), rowCt,ROWS(splitPositions), startPositions,INDEX(splitPositions,SEQUENCE(rowCt-1)), endPositions,INDEX(splitPositions,SEQUENCE(rowCt-1,1,2)), chunks, MID( searchTxt, startPositions, endPositions-startPositions ), findIndices,MATCH(chunks,findList,0), finds,INDEX(findList,findIndices), repls,INDEX(replaceList,findIndices), replace, IF( ISERROR( INDEX( finds, SEQUENCE(rowCt-1) ) ), chunks, repls ), join,IFERROR(TEXTJOIN("",TRUE,replace),searchTxt), join ) )(A5,things,replace)
B5:B15	B5	=ReplaceStrings(A5,things,replace)
C5:C15	C5	=ReplaceStrings.Loop(A5,things,replace)
B18:B28	B18	=ReplaceStrings.Delimiter(A5," ",things,replace)

Named Ranges
Name	Refers To	Cells
replace	=ContainsWhichThings!$F$5:$F$9	B1, B5:C15, B18:B28
things	=ContainsWhichThings!$E$5:$E$9	B1, B5:C15, B18:B28

 

[Xlambda]

Impresive work !! Wrote a recursive lambda that can handle the whole string column array
RPLAB(ar,a,b,i)=LAMBDA(ar,a,b,i,LET(r,ROWS(a),IF(i=r+1,ar,RPLAB(SUBSTITUTE(ar,INDEX(a,i),INDEX(b,i)),a,b,i+1))))
-replace a with b for array ar and i=1
If you don't like extra variable i we write another one REPLACEAB(ar,a,b)=LAMBDA(ar,a,b,RPLAB(ar,a,b,1))
Anyhow "i" can be very handy in case you want to change the index in the replace list. For ex. i=2 will ignore changing red with ugly.
Also the repl lists should be appended with PROPER variants if we want to replace the words that start with capital letters.✌