AKEEP

[Xlambda]

AKEEP array keep, keeps only certain chars of an array, all the others are removed and replaced with a delimiter or not. calls AREMOVE
Other functions used on minisheet ASPLIT , AUNQSRT , AFILTER , AAGGREGATE

=LAMBDA(a,k,d,
    LET(x,CONCAT(AREMOVE(a,k,)),l,LEN(x),
       m,SORT(UNIQUE(UNICODE(MID(x,SEQUENCE(l),1)))),
       AREMOVE(a,CONCAT(UNICHAR(m)),d)
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J
1	sample				=AKEEP(A2:A3,C3,)
2	akjhda786875, %&/jnlsdj(kjbkl)cg vhjk45678		keep only		77%g7
3	hghg354678· %&/hghj_>Z3s54dr6fty8 6545DRFTGHJ		g7H%		gg7%gH
4
5	extract unique dates for each row in ascending order
6	dfg><rd23/3/21-uy21/3/21g-·$%&23/03/21CVrt%&
7	05/04/21ftgy$%&02/04/21gkh-<·$%&2/4/21
8
9	=AKEEP(A6:A7,T_CHARS(1,,"/"),"-")		=ASPLIT(A10#,"-")				=AUNQSRT(AUNQSRT(C10#,),1)
10	23/3/21-21/3/21-23/03/21		44278	44276	44278		21-03-21	23-03-21
11	05/04/21-02/04/21-2/4/21		44291	44288	44288		02-04-21	05-04-21
12
13	sum the values found on each row
14	25.465jdlk·$%DF-56.34hg$%TYT-ghdWERT"·$%%&46.5
15	-18.6JHGytr&%$-JHGFuye&%$45.17JHG-JKHuytr(&$
16
17	=AKEEP(A14:A15,T_CHARS(1,,"-."),"\")		=ASPLIT(A18#,"\")
18	25.465\-56.34\-\46.5		25.465	-56.34	-	46.5
19	-18.6\-\45.17\-		-18.6	-	45.17	-
20			=AFILTER(C18#,1,)				=AAGGREGATE(C21#,5,)
21	other lambdas used in the minisheet		25.465	-56.34	46.5		15.625
22	ASPLIT		-18.6	45.17			26.57
23	AUNQSRT
24	AFILTER
25	AAGGREGATE
26
AKEEP post

Cell Formulas
Range	Formula
E1,G20,C20,A17,C17,A9,G9,C9	E1	=FORMULATEXT(E2)
E2:E3	E2	=AKEEP(A2:A3,C3,)
A10:A11	A10	=AKEEP(A6:A7,T_CHARS(1,,"/"),"-")
C10:E11	C10	=ASPLIT(A10#,"-")
G10:H11	G10	=AUNQSRT(AUNQSRT(C10#,),1)
A18:A19	A18	=AKEEP(A14:A15,T_CHARS(1,,"-."),"\")
C18:F19	C18	=ASPLIT(A18#,"\")
C21:E22	C21	=AFILTER(C18#,1,)
G21:G22	G21	=AAGGREGATE(C21#,5,)
Dynamic array formulas.

 

[Xlambda]

New function non recursive, same functionality, same arguments, now named: ar,ka (array and keep array) arguments, can be 2D arrays, d, delimiter argument can be omitted. Calls last AREMOVE.
Keeps only certain chars of an array, all the others are removed and replaced with a delimiter or not
Other functions on mini-sheet last T_CHARS

=LAMBDA(a,ka,[d],LET(k,TEXTJOIN("",,ka),x,TEXTJOIN("",,AREMOVE(a,k)),y,AREMOVE(a,x,d),IFERROR(--y,y)))

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	keeping bunch of chars							keeping bunch of chars, other chars,
2	no delimiter replacement, d,omitted							replaced with a string delimiter
3	sample array							sample array
4	abCD123	xyXY456		Note: function consistent with				xy23abcd43FG	52ghjt32XY
5	efEF567	ghGH890		numbers, whenever the case				54ATdfDFat91	28VBNvbn13
6	keep digits			check
7	=AKEEP(A4:B5,T_CHARS(1))			=ISNUMBER(A8#)				keep numbers
8	123	456		TRUE	TRUE			d,"|"
9	567	890		TRUE	TRUE			=AKEEP(H4:I5,T_CHARS(1),"|")
10	keep lower letters							23|43	52|32
11	=AKEEP(A4:B5,T_CHARS(,-1))			keep letters				54|91	28|13
12	ab	xy		=AKEEP(A4:B5,T_CHARS(,2))				any "bunch" of char not kept, is replaced only with one delimiter
13	ef	gh		abCD	xyXY
14	keep capital letters			efEF	ghGH			keep letters
15	=AKEEP(A4:B5,T_CHARS(,1))							d,"-|-"
16	CD	XY						=AKEEP(H4:I5,T_CHARS(,2),"-|-")
17	EF	GH						xy-|-abcd-|-FG	ghjt-|-XY
18				other functions				ATdfDFat	VBNvbn
19	ka,keep array as array			last T_CHARS				keep lower letters			keep upper letters
20								=AKEEP(H4:I5,T_CHARS(,-1),"-|-")			=AKEEP(H4:I5,T_CHARS(,1),"-|-")
21	ka,array							xy-|-abcd	ghjt		FG	XY
22	A	G		=AKEEP(A4:B5,A22:B28)				df-|-at	vbn		AT-|-DF	VBN
23	B	H		CD123	xyXY			any "bunch" of char removed, is replaced only with the string delimiter
24	C	X		EF	GH890
25	D	Y
26	E	x
27	F	y
28	123890
29
new AKEEP post 1

Cell Formulas
Range	Formula
A7,D7,D22,H20,K20,H16,A15,D12,A11,H9	A7	=FORMULATEXT(A8)
A8:B9	A8	=AKEEP(A4:B5,T_CHARS(1))
D8:E9	D8	=ISNUMBER(A8#)
H10:I11	H10	=AKEEP(H4:I5,T_CHARS(1),"|")
A12:B13	A12	=AKEEP(A4:B5,T_CHARS(,-1))
D13:E14	D13	=AKEEP(A4:B5,T_CHARS(,2))
A16:B17	A16	=AKEEP(A4:B5,T_CHARS(,1))
H17:I18	H17	=AKEEP(H4:I5,T_CHARS(,2),"-|-")
H21:I22	H21	=AKEEP(H4:I5,T_CHARS(,-1),"-|-")
K21:L22	K21	=AKEEP(H4:I5,T_CHARS(,1),"-|-")
D23:E24	D23	=AKEEP(A4:B5,A22:B28)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G
1	Task 1: extracting numbers, (negative/positive decimal numbers)
2	(keeping digits + representative chars
3	used in number constructions, like "-"/"+" minus/plus sign, or ".",decimal point)
4	sample
5	dVy,-24.75,ABC,13.5, -.35,XyzW,+23.54		other functions
6	89.45Abc,yT-123,456xYaB,+76.432,bGh		last T_CHARS
7			ATEXTSPLIT
8	T_CHARS with extra chars "-+."
9	=T_CHARS(1,,"-+.")
10	1234567890-+.
11
12	=AKEEP(A5:A6,A10,"/")		=ATEXTSPLIT(A13#,"/")
13	-24.75/13.5/-.35/+23.54		-24.75	13.5	-0.35	23.54
14	89.45/-123/456/+76.432		89.45	-123	456	76.432
15
16	single cell		Check	⬆
17	=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")		=ISNUMBER(C13#)
18	-24.75/13.5/-.35/+23.54		TRUE	TRUE	TRUE	TRUE
19	89.45/-123/456/+76.432		TRUE	TRUE	TRUE	TRUE
20
21	if negative nr. format is between "()"		TRUE	TRUE	TRUE	TRUE
22	sample		TRUE	TRUE	TRUE	TRUE
23	dVy,(24.75),ABC,13.5, (.35),XyzW,+23.54		=ISNUMBER(C27#)
24	89.45Abc,yT(123),456xYaB,+76.432,bGh		Check	⬇
25
26	=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")		=ATEXTSPLIT(A27#,"/")
27	(24.75)/13.5/(.35)/23.54		-24.75	13.5	-0.35	23.54
28	89.45/(123)/456/76.432		89.45	-123	456	76.432
29
new AKEEP post 2

Cell Formulas
Range	Formula
A9,A26,C26,A17,C17,A12,C12	A9	=FORMULATEXT(A10)
A10	A10	=T_CHARS(1,,"-+.")
A13:A14	A13	=AKEEP(A5:A6,A10,"/")
C13:F14,C27:F28	C13	=ATEXTSPLIT(A13#,"/")
A18:A19	A18	=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")
C18:F19	C18	=ISNUMBER(C13#)
C21:F22	C21	=ISNUMBER(C27#)
C23	C23	=FORMULATEXT(C21)
A27:A28	A27	=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G
1	Task 1: extracting numbers, (negative/positive decimal numbers)
2	(keeping digits + representative chars
3	used in number constructions, like "-"/"+" minus/plus sign, or ".",decimal point)
4	sample
5	dVy,-24.75,ABC,13.5, -.35,XyzW,+23.54		other functions
6	89.45Abc,yT-123,456xYaB,+76.432,bGh		last T_CHARS
7			ATEXTSPLIT
8	T_CHARS with extra chars "-+."
9	=T_CHARS(1,,"-+.")
10	1234567890-+.
11
12	=AKEEP(A5:A6,A10,"/")		=ATEXTSPLIT(A13#,"/")
13	-24.75/13.5/-.35/+23.54		-24.75	13.5	-0.35	23.54
14	89.45/-123/456/+76.432		89.45	-123	456	76.432
15
16	single cell		Check	⬆
17	=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")		=ISNUMBER(C13#)
18	-24.75/13.5/-.35/+23.54		TRUE	TRUE	TRUE	TRUE
19	89.45/-123/456/+76.432		TRUE	TRUE	TRUE	TRUE
20
21	if negative nr. format is between "()"		TRUE	TRUE	TRUE	TRUE
22	sample		TRUE	TRUE	TRUE	TRUE
23	dVy,(24.75),ABC,13.5, (.35),XyzW,+23.54		=ISNUMBER(C27#)
24	89.45Abc,yT(123),456xYaB,+76.432,bGh		Check	⬇
25
26	=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")		=ATEXTSPLIT(A27#,"/")
27	(24.75)/13.5/(.35)/23.54		-24.75	13.5	-0.35	23.54
28	89.45/(123)/456/76.432		89.45	-123	456	76.432
29
new AKEEP post 2

Cell Formulas
Range	Formula
A9,A26,C26,A17,C17,A12,C12	A9	=FORMULATEXT(A10)
A10	A10	=T_CHARS(1,,"-+.")
A13:A14	A13	=AKEEP(A5:A6,A10,"/")
C13:F14,C27:F28	C13	=ATEXTSPLIT(A13#,"/")
A18:A19	A18	=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")
C18:F19	C18	=ISNUMBER(C13#)
C21:F22	C21	=ISNUMBER(C27#)
C23	C23	=FORMULATEXT(C21)
A27:A28	A27	=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H
1	Task 2: extracting numbers with commas as thousands separators format
2	what if numbers have thounsands separators ","
3	and same "," is used as delimiter		other functions
4	sample		last T_CHARS
5	Cdxy,-1,345.23,xyAB,123,465.35zxWQ,(.2)		ATEXTSPLIT
6	rtY(1,023,456),nmMN23,575.20kL,+45,108.05		AREMOVE
7	Note: If we remove the ",", numbers with thousands separators will not be affected
8	=AREMOVE(A5:A6,",")
9	Cdxy-1345.23xyAB123465.35zxWQ(.2)
10	rtY(1023456)nmMN23575.20kL+45108.05
11	after we remove ",", we can reuse it as delimiter
12	=AKEEP(A9#,T_CHARS(1,,"().-+"),",")		=ATEXTSPLIT(A13#,",")
13	-1345.23,123465.35,(.2)		-1345.23	123465.35	-0.2
14	(1023456),23575.20,+45108.05		-1023456	23575.2	45108.05
15
16	single cell formula
17	=AKEEP(AREMOVE(A5:A6,","),T_CHARS(1,,"().-+"),",")		=ATEXTSPLIT(A18#,",")
18	-1345.23,123465.35,(.2)		-1345.23	123465.35	-0.2
19	(1023456),23575.20,+45108.05		-1023456	23575.2	45108.05
20
21			Check both arrays
22			=AND(ISNUMBER(C13#),ISNUMBER(C18#))
23			TRUE
24
25	Dates complex scenarios extraction will follow with the help of the new AREPLACE, AOCREPLACE functions !!
26
new AKEEP post 3

Cell Formulas
Range	Formula
A8,C22,A17,C17,A12,C12	A8	=FORMULATEXT(A9)
A9:A10	A9	=AREMOVE(A5:A6,",")
A13:A14	A13	=AKEEP(A9#,T_CHARS(1,,"().-+"),",")
C13:E14,C18:E19	C13	=ATEXTSPLIT(A13#,",")
A18:A19	A18	=AKEEP(AREMOVE(A5:A6,","),T_CHARS(1,,"().-+"),",")
C23	C23	=AND(ISNUMBER(C13#),ISNUMBER(C18#))
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Task 3: Single cell formula to extract numbers from array using combo AREMOVE/AKEEP
2	number formats that formula should handle:
3	- positive,negative,decimal,negative (123), thousands separators (1,234,567.34) ,scientific using exponent E (1.23E+5, -1.23E-5)
4	sample
5	Aa+2,321,123.25tY,rT(25,678.35)vwQS	vX-754.65nME-(cD),wS-1.23E-5pL			step 3: splitting for ","
6	E-(dGh-K]-1,100,101.03,xZ5.75E+4fR	fHj(2.34E-6)bNM,zSD234.345qWE			=ATEXTSPLIT(A15#,",")
7					2321123.3	-25678.35
8	step 1: removing thousand separators				-754.65	E-(	)	-1.23E-05
9	=AREMOVE(A5:B6,",")				E-(	-	-1100101	57500
10	Aa+2321123.25tYrT(25678.35)vwQS	vX-754.65nME-(cD)wS-1.23E-5pL			-2.34E-06	234.345	E
11	E-(dGh-K]-1100101.03xZ5.75E+4fR	fHj(2.34E-6)bNMzSD234.345qWE
12					step 4: filtering numbers only
13	step 2: keeping digits and chars used in number representations ".+-()E", replacing others with ","				=AFUSBYROW(E7#,1)
14	=AKEEP(A10#,T_CHARS(1,,".+-()E"),",")				2321123.3	-25678.35
15	+2321123.25,(25678.35)	-754.65,E-(,),-1.23E-5			-754.65	-0.0000123
16	E-(,-,-1100101.03,5.75E+4	(2.34E-6),234.345,E			-1100101	57500
17					-2.34E-06	234.345
18
19	functions on mini-sheet				step 5: resizing
20	AKEEP				=ARESIZE(E14#,2,4)
21	AREMOVE				2321123.3	-25678.35	-754.65	-1.23E-05
22	ATEXTSPLIT				-1100101	57500	-2.34E-06	234.345
23	AFUSBYROWS
24	ARESIZE				single cell
25	T_CHARS	=ARESIZE(AFUSBYROW(ATEXTSPLIT(AKEEP(AREMOVE(A5:B6,","),T_CHARS(1,,".+-()E"),","),","),1),2,4)
26					2321123.3	-25678.35	-754.65	-1.23E-05
27					-1100101	57500	-2.34E-06	234.345
28
new AKEEP post 4

Cell Formulas
Range	Formula
E6,E20,A14,E13,A9	E6	=FORMULATEXT(E7)
E7:H10	E7	=ATEXTSPLIT(A15#,",")
A10:B11	A10	=AREMOVE(A5:B6,",")
E14:F17	E14	=AFUSBYROW(E7#,1)
A15:B16	A15	=AKEEP(A10#,T_CHARS(1,,".+-()E"),",")
E21:H22	E21	=ARESIZE(E14#,2,4)
B25	B25	=FORMULATEXT(E26)
E26:H27	E26	=ARESIZE(AFUSBYROW(ATEXTSPLIT(AKEEP(AREMOVE(A5:B6,","),T_CHARS(1,,".+-()E"),","),","),1),2,4)
Dynamic array formulas.