AOCREPLACE

[Xlambda]

AOCREPLACE !! recursive !! array occurrences replace, replaces only certain occurrences "oc" of a delimiter(any string) "d" in array "a", with a replacement delimiter(any string) "rd"

=LAMBDA(a,d,rd,oc,
    LET(y,SORT(UNIQUE(oc,1),,,1),o,FILTER(y,y>0,0),n,COLUMNS(o),x,INDEX(o,1,n),
       IF(n=1,IF(o=0,SUBSTITUTE(a,d,rd),SUBSTITUTE(a,d,rd,x)),AOCREPLACE(SUBSTITUTE(a,d,rd,x),d,rd,INDEX(o,1,SEQUENCE(,n-1))))
    )
)

LAMBDA 6.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Sample
2	19/3/2021/ES-3765/Tv-LG345/375				extract values with ASPLIT		ASPLIT
3	20/3/2021/FR-5545678/Mobile-SM76345/1233		=AOCREPLACE(A2:A4,"/","|",{3,4,5})		=ASPLIT(C4#,"|")
4	21/3/2021/AG-465/Audio-BO6345/1980		19/3/2021|ES-3765|Tv-LG345|375		19-03-21	ES-3765	Tv-LG345	375
5			20/3/2021|FR-5545678|Mobile-SM76345|1233		20-03-21	FR-5545678	Mobile-SM76345	1233
6	=AOCREPLACE(A2:A4,"/","|",)		21/3/2021|AG-465|Audio-BO6345|1980		21-03-21	AG-465	Audio-BO6345	1980
7	19|3|2021|ES-3765|Tv-LG345|375
8	20|3|2021|FR-5545678|Mobile-SM76345|1233				year in a separate column
9	21|3|2021|AG-465|Audio-BO6345|1980		=AOCREPLACE(A2:A4,"/","|",{2,3,4,5})		=ASPLIT(C10#,"|")
10			19/3|2021|ES-3765|Tv-LG345|375		19-03-21	2021	ES-3765	Tv-LG345	375
11	=AOCREPLACE(A2:A4,"/","|",{-3,0,2,2,4,7})		20/3|2021|FR-5545678|Mobile-SM76345|1233		20-03-21	2021	FR-5545678	Mobile-SM76345	1233
12	19/3|2021/ES-3765|Tv-LG345/375		21/3|2021|AG-465|Audio-BO6345|1980		21-03-21	2021	AG-465	Audio-BO6345	1980
13	20/3|2021/FR-5545678|Mobile-SM76345/1233
14	21/3|2021/AG-465|Audio-BO6345/1980				extract everything except dates
15			=AOCREPLACE(A2:A4,"-","/",)		=ASPLIT(AOCREPLACE(C16#,"/","|",{3,4,5,6,7}),"|")
16			19/3/2021/ES/3765/Tv/LG345/375		19-03-21	ES	3765	Tv	LG345	375
17			20/3/2021/FR/5545678/Mobile/SM76345/1233		20-03-21	FR	5545678	Mobile	SM76345	1233
18			21/3/2021/AG/465/Audio/BO6345/1980		21-03-21	AG	465	Audio	BO6345	1980
19
20			=AOCREPLACE(AOCREPLACE(A21:A24,"//","|",),"/"," ",)		=ASPLIT(C21#,"|")
21	aa//gf//bh//nm/ng/dghf/gf		aa|gf|bh|nm ng dghf gf		aa	gf	bh	nm ng dghf gf
22	aa//gf//bh//nm/ng/dghf/gf		aa|gf|bh|nm ng dghf gf		aa	gf	bh	nm ng dghf gf
23	aa//gf//bh//nm/ng/dghf/gf		aa|gf|bh|nm ng dghf gf		aa	gf	bh	nm ng dghf gf
24	aa//gf//bh//nm/ng/dghf/gf		aa|gf|bh|nm ng dghf gf		aa	gf	bh	nm ng dghf gf
25
AOCREPLACE post

Cell Formulas
Range	Formula
C3,C20,E20,C15,E15,A11,C9,E9,A6,E3	C3	=FORMULATEXT(C4)
C4:C6	C4	=AOCREPLACE(A2:A4,"/","|",{3,4,5})
E4:H6,E21:H24,E10:I12	E4	=ASPLIT(C4#,"|")
A7:A9	A7	=AOCREPLACE(A2:A4,"/","|",)
C10:C12	C10	=AOCREPLACE(A2:A4,"/","|",{2,3,4,5})
A12:A14	A12	=AOCREPLACE(A2:A4,"/","|",{-3,0,2,2,4,7})
C16:C18	C16	=AOCREPLACE(A2:A4,"-","/",)
E16:J18	E16	=ASPLIT(AOCREPLACE(C16#,"/","|",{3,4,5,6,7}),"|")
C21:C24	C21	=AOCREPLACE(AOCREPLACE(A21:A24,"//","|",),"/"," ",)
Dynamic array formulas.

 

[smozgur]

Note: The following Lambda function is used in the sample mini-sheet above are explained on the following page:
ASPLIT