ATRIM

[Xlambda]

ATRIM array trim, does for any delimiter what TRIM does to space chars, plus can CLEAN any non printable chars, replaces non breaking spaces CHAR(160), with regular spaces.

=LAMBDA(a,d,c,
    LET(x,OR(c={0,1}),h,CHAR(1),
      ax,IF(c,SUBSTITUTE(CLEAN(a),CHAR(160)," "),a),
      ay,IF(OR(d=0,d=""),ax,SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(ax)," ",CHAR(1)),d," "))," ",d),CHAR(1)," ")),
      IF(x,ay,"check values")
    )
)

LAMBDA 6.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		CLEAN works								ok	CLEAN works					no CLEAN
3				line feed	c return					space						non breaking space
4	sample CODE values	2	9	10	13	28	29	30	31	32	129	141	143	144	157	160
5	="ab"&CHAR(B4:P4)&"cd"	abcd	ab cd	ab cd	ab cd	abcd	abcd	abcd	abcd	ab cd	abcd	abcd	abcd	abcd	abcd	ab cd
6	=LEN(B5#)	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
7
8	=ATRIM(B5#,,1)	abcd	abcd	abcd	abcd	abcd	abcd	abcd	abcd	ab cd	abcd	abcd	abcd	abcd	abcd	ab cd
9	=LEN(B8#)	4	4	4	4	4	4	4	4	5	4	4	4	4	4	5
10
11									sample		=ATRIM(I12:J14,"-",)			sample			=ATRIM(N12:N14,"//",)
12		="--sd"&CHAR(1)&"fr--"&CHAR(129)&"--4"&CHAR(160)&"5--fvb#45--"							--r 67--t--	---pb 18-h	r 67-t	pb 18-h		//iut yt56//rtujh45////sdfg$%456			iut yt56//rtujh45//sdfg$%456
13		--sdfr----4 5--fvb#45--							s 56---v-	xy 78-n	s 56-v	xy 78-n		rtyu//ghj //46ghh// yuyt //ghfd65			rtyu//ghj //46ghh// yuyt //ghfd65
14									-t 89-w	gh 98--y--	t 89-w	gh 98-y		//34%&/dfg/////jhgf////dfg345			34%&/dfg///jhgf//dfg345
15		=ATRIM(B13,"-",1)
16		sdfr-4 5-fvb#45
17
ATRIM post

Cell Formulas
Range	Formula
A5:A6,A8:A9	A5	=FORMULATEXT(B5)
B5:P5	B5	="ab"&CHAR(B4:P4)&"cd"
B6:P6,B9:P9	B6	=LEN(B5#)
B8:P8	B8	=ATRIM(B5#,,1)
K11,B15,Q11,B12	K11	=FORMULATEXT(K12)
K12:L14	K12	=ATRIM(I12:J14,"-",)
Q12:Q14	Q12	=ATRIM(N12:N14,"//",)
B13	B13	="--sd"&CHAR(1)&"fr--"&CHAR(129)&"--4"&CHAR(160)&"5--fvb#45--"
B16	B16	=ATRIM(B13,"-",1)
Dynamic array formulas.

 

[Xlambda]

Basically, same functionality, same arguments, "c", (clean argument) can be omitted now, added a some tweaks:
- added, apart of CHAR(160), CHAR(127), the DEL command that also can not be cleaned by CLEAN function
- like ATEXTJOIN,ATEXTSPLIT, new ATRIM is keeping the spaces structure, previous formula was trimming spaces.

=LAMBDA(a,d,[c],
    LET(h,CHAR(1),x,IF(c,SUBSTITUTE(SUBSTITUTE(CLEAN(a),CHAR(160)," "),CHAR(127),""),a),
       y,IF(d="",x,SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(x," ",h),d," "))," ",d),h," ")),
       IF(ISNUMBER(--y),--y,y)
    )
)

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	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	codes of control/"special" chars between [1,255]
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	127	129	141	143	144	157	160
3	=CHAR(A2:AM2)
4
5	="/"&A4#&"\"
6	/\	/\	/\	/\	/\	/\	/ \	/\	/ \	/ \	/ \	/\	/ \	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/ \	/\	/\	/\	/\	/\	/\	/ \
7	=LEN(A6#)
8	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
9	=ATRIM(A6#,,1)
10	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/\	/ \	/\	/\	/\	/\	/\	/\	/ \
11	=LEN(A10#)
12	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	3	2	2	2	2	2	2	3
13	As we see, checking with LEN functions, ATRIM "cleans" properly, space remains as space, like any other visible char, and non breaking space replaced with a space.
14
15	common "special" chars
16	7	bell or screen flashing
17	8	backspace
18	9	horizontal tab
19	10	line feed
20	11	vertical tab
21	12	form feed
22	13	carriage return
23	26	Ctrl-Z
24	27	escape
25	32	space (acts like any "visible" char)
26	127	DEL command, does not get CLEANed
27	160	non breaking space, does not get CLEANed
28
new ATRIM post 1

Cell Formulas
Range	Formula
A3,A11,A9,A7,A5	A3	=FORMULATEXT(A4)
A4:AM4	A4	=CHAR(A2:AM2)
A6:AM6	A6	="/"&A4#&"\"
A8:AM8,A12:AM12	A8	=LEN(A6#)
A10:AM10	A10	=ATRIM(A6#,,1)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J
1		sample		=ATRIM(B2,",",1)		=TRIM(ATRIM(B2,",",1))
2		,, ,, ,b , c,,, ,,, ,		, , ,b , c, , ,		, , ,b , c, , ,
3
4		=LEN(B2)		=LEN(D2)		=LEN(F2)
5		26		20		15
6				spaces structure is kept		extra spaces trimming only by choice
7
8	Note: Cell B3 has leading space, trailing space, and some inner spaces also.
9	Since we trim for "," delimiter, spaces structure is kept intact,only "," are trimmed
10	Space trimming by choice, with a final TRIM
11
12		array			=ATRIM(B13:C14,",")
13		,,a,,,b,c,,, , ,e,, f,	,,A,,B,,C,,		a,b,c, , ,e, f	A,B,C
14		1,,2,,3,,4,,,,5,,6	Ax2,, ,,By3		1,2,3,4,5,6	Ax2, ,By3
15
16					=ATRIM(C17:C18,",")		=ISNUMBER(E17#)
17			,,,-32.576,,		-32.576		TRUE
18			,,,,1E+3,,,,		1000		TRUE
19
20					Note: Also, like ATEXTSPLIT, function is consistent
21					with numbers, whenever is the case
22
23			dbl delimiter		=ATRIM(C24,"//")
24			06////09///a		06//09///a
25					only multiple of "//" are trimmed
26
new ATRIM post 2

Cell Formulas
Range	Formula
D1,F1,E23,E16,G16,E12,F4,D4,B4	D1	=FORMULATEXT(D2)
D2	D2	=ATRIM(B2,",",1)
F2	F2	=TRIM(ATRIM(B2,",",1))
B5,F5,D5	B5	=LEN(B2)
E13:F14	E13	=ATRIM(B13:C14,",")
E17:E18	E17	=ATRIM(C17:C18,",")
G17:G18	G17	=ISNUMBER(E17#)
E24	E24	=ATRIM(C24,"//")
Dynamic array formulas.