AREPLACE

[Xlambda]

AREPLACE !! recursive!! (array replace), replaces all occurrences of "a" found in "ar" with values of "b"

=LAMBDA(ar,a,b,
    LET(n,ROWS(a),
       x,INDEX(a,n),y,INDEX(b,n),
       IF(n=1,SUBSTITUTE(ar,x,y),AREPLACE(SUBSTITUTE(ar,x,y),INDEX(a,SEQUENCE(n-1)),INDEX(b,SEQUENCE(n-1))))
   )
)

LAMBDA 6.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Sample					Lambda that appends string values of an array with its PROPER values.
2	=IF(SEQUENCE(7),"Aaa aaa Bbb bbb Ccc ccc Ddd ddd")					T_PROPER(a)=LAMBDA(a,LET(x,IF(ROWS(a)=1,TRANSPOSE(a),a),p,PROPER(a),n,ROWS(x),s,SEQUENCE(2*n)-n,IF(s<=0,x,INDEX(p,s))))
3	Aaa aaa Bbb bbb Ccc ccc Ddd ddd
4	Aaa aaa Bbb bbb Ccc ccc Ddd ddd								replace	aaa	bbb	ccc	ddd
5	Aaa aaa Bbb bbb Ccc ccc Ddd ddd								with	xx	yy	zz	ww
6	Aaa aaa Bbb bbb Ccc ccc Ddd ddd
7	Aaa aaa Bbb bbb Ccc ccc Ddd ddd							I12	=T_PROPER(J4:M4)
8	Aaa aaa Bbb bbb Ccc ccc Ddd ddd							J12	=T_PROPER(J5:M5)
9	Aaa aaa Bbb bbb Ccc ccc Ddd ddd
10							=AREPLACE(A3#,T_PROPER(C14:C17),T_PROPER(D14:D17))
11	=AREPLACE(A3#,C14:C17,D14:D17)						Xx xx Yy yy Zz zz Ww ww		rpl P	with P
12	Aaa xx Bbb yy Ccc zz Ddd ww						Xx xx Yy yy Zz zz Ww ww		aaa	xx
13	Aaa xx Bbb yy Ccc zz Ddd ww		replace	with			Xx xx Yy yy Zz zz Ww ww		bbb	yy
14	Aaa xx Bbb yy Ccc zz Ddd ww		aaa	xx			Xx xx Yy yy Zz zz Ww ww		ccc	zz
15	Aaa xx Bbb yy Ccc zz Ddd ww		bbb	yy			Xx xx Yy yy Zz zz Ww ww		ddd	ww
16	Aaa xx Bbb yy Ccc zz Ddd ww		ccc	zz			Xx xx Yy yy Zz zz Ww ww		Aaa	Xx
17	Aaa xx Bbb yy Ccc zz Ddd ww		ddd	ww			Xx xx Yy yy Zz zz Ww ww		Bbb	Yy
18	Aaa xx Bbb yy Ccc zz Ddd ww								Ccc	Zz
19									Ddd	Ww
20	=AREPLACE(A3#,AFLATTEN(C23:D24),E23:E26)
21	Aaa xx Bbb yy Ccc zz Ddd ww
22	Aaa xx Bbb yy Ccc zz Ddd ww		replace		with
23	Aaa xx Bbb yy Ccc zz Ddd ww		aaa	bbb	xx
24	Aaa xx Bbb yy Ccc zz Ddd ww		ccc	ddd	yy
25	Aaa xx Bbb yy Ccc zz Ddd ww				zz
26	Aaa xx Bbb yy Ccc zz Ddd ww				ww
27	Aaa xx Bbb yy Ccc zz Ddd ww
28
AREPLACE post

Cell Formulas
Range	Formula
A2,A20,G10,A11	A2	=FORMULATEXT(A3)
A3:A9	A3	=IF(SEQUENCE(7),"Aaa aaa Bbb bbb Ccc ccc Ddd ddd")
I7	I7	=FORMULATEXT(I12)
I8	I8	=FORMULATEXT(J12)
G11:G17	G11	=AREPLACE(A3#,T_PROPER(C14:C17),T_PROPER(D14:D17))
A12:A18	A12	=AREPLACE(A3#,C14:C17,D14:D17)
I12:I19	I12	=T_PROPER(J4:M4)
J12:J19	J12	=T_PROPER(J5:M5)
A21:A27	A21	=AREPLACE(A3#,AFLATTEN(C23:D24),E23:E26)
Dynamic array formulas.

 

[smozgur]

Note: The following Lambda function is used in the sample mini-sheet above.
AFLATTEN

T_PROPER Lambda function is provided in Cell F2.

 

[Xlambda]

AREPLACE(ar,a,[b ],[cs],[in])
Brand new function, 4 in 1, replace with an array, replace with delimiter, remove, insert. Same name, more arguments, added functionality, non recursive. NEW!! 2d REDUCE. Calls AFLAT.
REDUCE uses as "initial value" argument a 2D array.
Replaces all occurrences of words of array "a" found in "ar" with their correspondent values of array "b"
ar: array
a: array
[b ]: array
new arguments:
[cs]: case sensitive argument
[in]: insert argument

=LAMBDA(ar,a,[b],[cs],[in],
     LET(x,AFLAT(a),f,AFLAT(b,1),y,IF(in,x&f,f),r,SEQUENCE(ROWS(x)),xl,LOWER(x),xc,UPPER(x),xp,PROPER(x),
        xx,IF(in,x,IFS(cs=0,x,cs=2,CHOOSE({1,2,3},x,xl,xc),OR(cs=-1,cs=1,cs=3),CHOOSE({1,2},x,SWITCH(cs,-1,xl,1,xc,3,xp)))),c,SEQUENCE(COLUMNS(xx)),
        z,REDUCE(ar,c,LAMBDA(a,i,REDUCE(a,r,LAMBDA(a,j,SUBSTITUTE(a,INDEX(xx,j,i),IFERROR(INDEX(y,IF(ROWS(y)=1,1,j)),"")))))),
        IF(AND(ISNA(xx)),"check cs",IFERROR(--z,z))
    )
)

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	new!! AREPLACE functionality using 2D REDUCE, 4 in 1 function, replace,replace with delimiter,remove,insert
2	Replaces all occurrences of words of array "a" found in "ar" with their correspondent values of array "b"
3	If AREMOVE and AKEEP refers to chars, this function refers to whole words
4	[cs]: case sensitive argument
5	[in]: insert argument
6	Part 1: how correspondent arrays a,b work, when "cs" and "in" arguments omitted
7
8	ar,sample			a
9	rd45<red>,<cyan>cy34	<blue>le32,ya66<cyan>		red
10	een27<green>,bl87<blue>	dr56<red>,ne74<green>		green
11				blue
12	b,omitted, remove words functionality			cyan
13	=AREPLACE(A9:B10,D9:D12)
14	rd45<>,<>cy34	<>le32,ya66<>
15	een27<>,bl87<>	dr56<>,ne74<>
16
17	b,"-|-",one element, replace with a delimiter functionality
18	=AREPLACE(A9:B10,D9:D12,"-|-")			b
19	rd45<-|->,<-|->cy34	<-|->le32,ya66<-|->		-|-
20	een27<-|->,bl87<-|->	dr56<-|->,ne74<-|->
21				b
22	b, array , same nr. of elements with "a"			clr1
23	=AREPLACE(A9:B10,D9:D12,D22:D25)			clr2
24	rd45<clr1>,<clr4>cy34	<clr3>le32,ya66<clr4>		clr3
25	een27<clr2>,bl87<clr3>	dr56<clr1>,ne74<clr2>		clr4
26
27	b1,array , more than 1 element but less than "a" elements
28	=AREPLACE(A9:B10,D9:D12,D29:D30)			b1		b2		b3
29	rd45<clr1>,<>cy34	<>le32,ya66<>		clr1		clr1		clr1
30	een27<clr2>,bl87<>	dr56<clr1>,ne74<clr2>		clr2				clr2
31						clr3		clr3
32	b2 array with "gaps"					clr4		clr4
33	=AREPLACE(A9:B10,D9:D12,F29:F32)							clr5
34	rd45<clr1>,<clr4>cy34	<clr3>le32,ya66<clr4>
35	een27<>,bl87<clr3>	dr56<clr1>,ne74<>
36
37	b3,array, more elements than "a"
38	=AREPLACE(A9:B10,D9:D12,H29:H33)
39	rd45<clr1>,<clr4>cy34	<clr3>le32,ya66<clr4>
40	een27<clr2>,bl87<clr3>	dr56<clr1>,ne74<clr2>
41	replace functionality
42	If rows(b)<rows(a), non correspondent values of "a" are replaced with null strings
43	If rows(b)>rows(a), extra values of "b" have no effect
44
new AREPLACE post 1

Cell Formulas
Range	Formula
A13,A38,A33,A28,A23,A18	A13	=FORMULATEXT(A14)
A14:B15	A14	=AREPLACE(A9:B10,D9:D12)
A19:B20	A19	=AREPLACE(A9:B10,D9:D12,"-|-")
A24:B25	A24	=AREPLACE(A9:B10,D9:D12,D22:D25)
A29:B30	A29	=AREPLACE(A9:B10,D9:D12,D29:D30)
A34:B35	A34	=AREPLACE(A9:B10,D9:D12,F29:F32)
A39:B40	A39	=AREPLACE(A9:B10,D9:D12,H29:H33)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Part 2: "cs" case sensitive argumend functionality.
2	Since the core of the function is the SUBSTITUTE function, the function is case sensitive also.
3	What function does, expands the "a" array with its correspondent values acording to "cs" values
4	cs: 0 or omitted will leave the "a" array as it is
5	cs: -1, expands "a" with an extra column of lower(a)
6	cs: 1 expands "a" with an extra column of upper(a)
7	cs: 2, expands "a" with 2 columns lower(a)+upper(a)
8	cs: 3, expands "a" with an extra column of proper(a)
9			new "a" calculated inside the function for different "cs" values
10	a		cs,0		cs,-1			cs,1			cs,2				cs,3
11	xYz		xYz		xYz	xyz		xYz	XYZ		xYz	xyz	XYZ		xYz	Xyz
12	aBc		aBc		aBc	abc		aBc	ABC		aBc	abc	ABC		aBc	Abc
13
14	The nested or 2D REDUCE part of the function, is able to handle the new"a" 2 dimensions whenever the case
15		[ z,REDUCE(ar,c,LAMBDA(a,i,REDUCE(a,r,LAMBDA(a,j,SUBSTITUTE(a,INDEX(xx,j,i),IFERROR(INDEX(y,IF(ROWS(y)=1,1,j)),"")))))) ]
16	To acomplish same functionality before REDUCE, only nested recursion for nr of rows/clms could have done it.
17
18	Previous AREPLACE function covered only PROPER versatility with a separate function,
19	new AREPLACE has it embedded in the function itself, plus lower/upper case versatility
20
21	sample				a		b
22	xYz,xyz	xYz,XYZ	xYz,Xyz		xYz		(1)
23	aBc,abc	aBc,ABC	aBc,Abc		aBc		(2)
24
25	cs,omitted				cs,-1				cs,1
26	=AREPLACE(A22:C23,E22:E23,G22:G23)				=AREPLACE(A22:C23,E22:E23,G22:G23,-1)				=AREPLACE(A22:C23,E22:E23,G22:G23,1)
27	(1),xyz	(1),XYZ	(1),Xyz		(1),(1)	(1),XYZ	(1),Xyz		(1),xyz	(1),(1)	(1),Xyz
28	(2),abc	(2),ABC	(2),Abc		(2),(2)	(2),ABC	(2),Abc		(2),abc	(2),(2)	(2),Abc
29
30	cs,wrong argument (-2)				cs,2				cs,3
31	=AREPLACE(A22:C23,E22:E23,G22:G23,-2)				=AREPLACE(A22:C23,E22:E23,G22:G23,2)				=AREPLACE(A22:C23,E22:E23,G22:G23,3)
32	check cs				(1),(1)	(1),(1)	(1),Xyz		(1),xyz	(1),XYZ	(1),(1)
33					(2),(2)	(2),(2)	(2),Abc		(2),abc	(2),ABC	(2),(2)
34
new AREPLACE post 2

Cell Formulas
Range	Formula
F11:F12,L11:L12	F11	=LOWER(E11:E12)
I11:I12	I11	=UPPER(H11:H12)
M11:M12	M11	=UPPER(K11:K12)
P11:P12	P11	=PROPER(O11:O12)
A26,E26,I26,A31,E31,I31	A26	=FORMULATEXT(A27)
A27:C28	A27	=AREPLACE(A22:C23,E22:E23,G22:G23)
E27:G28	E27	=AREPLACE(A22:C23,E22:E23,G22:G23,-1)
I27:K28	I27	=AREPLACE(A22:C23,E22:E23,G22:G23,1)
A32	A32	=AREPLACE(A22:C23,E22:E23,G22:G23,-2)
E32:G33	E32	=AREPLACE(A22:C23,E22:E23,G22:G23,2)
I32:K33	I32	=AREPLACE(A22:C23,E22:E23,G22:G23,3)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Part 3: "in", insert argument functionality.							How function calculates "new b" when in=1, 3 different scenarios
2	in: 0 or omitted, array "b" not altered, replace functionality							a		b1		new b1		b2		new b2		b3		new b3
3	in: 1 or <>0, new"b"=a&b, insert functionality							red		/		red/		/1		red/1		/		red/
4	Note: If in<>0, cs will take 0 value, has no effect							green				green/		/2		green/2		\		green\
5	Inserting is posible only for "a" values as they are.							blue				blue/		/3		blue/3				blue
6								cyan				cyan/		/4		cyan/4		|		cyan|
7					ar,sample 1
8	a		b		redAB,green27											cs,2,in,omitted, no inserting
9	red		/		blueXY,cyan34			a		b		ar,sample 2				=AREPLACE(L10:L11,H10:H13,J10:J13,2)
10	green							red		/1,		redAB,GREEN27				/1,AB,/2,27
11	blue				=AREPLACE(E8:E9,H3:H6,J3,,1)			green		/2,		BLUEXY,cyan34				/3,XY,/4,34
12	cyan				red/AB,green/27			blue		/3,
13					blue/XY,cyan/34			cyan		/4,						cs,2,in,1 ,inserting takes place
14			/1,													=AREPLACE(L10:L11,H10:H13,J10:J13,2,1)
15			/2,		=AREPLACE(E8:E9,A9:A12,C14:C17,,1)											red/1,AB,GREEN27
16			/3,		red/1,AB,green/2,27											BLUEXY,cyan/4,34
17			/4,		blue/3,XY,cyan/4,34							Because in=1, cs value has no effect, the formula considers it as a 0
18
19			/1,
20					=AREPLACE(E8:E9,A9:A12,C19:C22,,1)
21					red/1,AB,green27
22			/4,		blueXY,cyan/4,34
23
new AREPLACE post 3

Cell Formulas
Range	Formula
L3:L6	L3	=H3:H6&J3
P3:P6	P3	=H3:H6&N3:N6
T3:T6	T3	=IFNA(H3:H6&R3:R6,"")
P9,E20,E15,P14,E11	P9	=FORMULATEXT(P10)
P10:P11	P10	=AREPLACE(L10:L11,H10:H13,J10:J13,2)
E12:E13	E12	=AREPLACE(E8:E9,H3:H6,J3,,1)
P15:P16	P15	=AREPLACE(L10:L11,H10:H13,J10:J13,2,1)
E16:E17	E16	=AREPLACE(E8:E9,A9:A12,C14:C17,,1)
E21:E22	E21	=AREPLACE(E8:E9,A9:A12,C19:C22,,1)
Dynamic array formulas.