ATRIM

[schardt679]

ATRIM combines TRIM and CLEAN and removes all extra delimiters in text strings (like TRIM for spaces) and removes all non-printing characters listed in CLEAN.
It converts ASCII delete characters (CHAR 127) to empty strings and non-breaking spaces (CHAR 160) spaces (CHAR 32). Thanks to Xlambda and their ATRIM for the inspiration.

=LAMBDA(Array,Delimiter,
      LET(Arr, Array,
          Del, Delimiter&"",
          Sp, " ",
          C_127, CHAR(127),
          C_160, CHAR(160),
          No_C127, SUBSTITUTE(CLEAN(Arr), C_127, ""),
          No_C160, SUBSTITUTE(No_C127, C_160, Sp),
          DelToC127, TRIM(SUBSTITUTE(No_C160, Del, C_127)),
          SpToC160, SUBSTITUTE(DelToC127, Sp, C_160),
          C127ToSp, TRIM(SUBSTITUTE(SpToC160, C_127, Sp)),
          SpToDel, SUBSTITUTE(C127ToSp, Sp, Del),
          Return, SUBSTITUTE(SpToDel, C_160, Sp),
          Return
      )
 )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I
1		ATRIM
2
3		Original Data				Result
4		Product	Sales	Date		Product	Sales	Date
5		Pepper Deseeder	19	03/22/21		Pepper Deseeder	19	03/22/21
6		Knife Set	4	03/22/21		Knife Set	4	03/22/21
7		Cutting Board	7	03/22/21		Cutting Board	7	44277
8		Pepper Deseeder	#N/A	03/23/21		Pepper Deseeder	#N/A	03/23/21
9		Knife Set	28	03/23/21		Knife Set	28	44278
10		Cutting Board	16	03/23/21		Cutting Board	16	03/23/21
11		Pepper Deseeder		03/24/21		Pepper Deseeder		44279
12
13		Delimiter is space
14		Formula in cell F4☛ =ATRIM(B4:D11,)
15
16
17
18		Original Data		Result
19		Date		Date
20		03/22///21		03/22/21
21		#VALUE!		#VALUE!
22		03//22///21		03/22/21
23		03//23/21		03/23/21
24
25		03/23/21		03/23/21
26		03/24/21		44279
27
28		Delimiter is slash
29		Formula in cell D19☛ =ATRIM(B19:B26, "/")
30
31
32
33		Original Data		Result
34		Group||Project|||Start By||||End By		Group|Project|Start By|End By
35		1||||A|||3/22/21|||3/23/21		1|A|3/22/21|3/23/21
36
37		#VALUE!		#VALUE!
38		2|||||B2|||||3/22/21|||||3/25/21		2|B2|3/22/21|3/25/21
39		3||C|||3/23/21||||3/25/21		3|C|3/23/21|3/25/21
40		4||||D|||3/23/21||||3/23/21		4|D|3/23/21|3/23/21
41		5||||E1|||||3/23/21||||3/26/21		5|E1|3/23/21|3/26/21
42		5||||E2||||||3/24/21|||3/29/21		5|E2|3/24/21|3/29/21
43		5||||||E3||3/25/21|||3/30/21		5|E3|3/25/21|3/30/21
44
45		Delimiter is pipe
46		Formula in cell D34☛ =ATRIM(B34:B43, "|")
47
48
49
50		Original Data		Result
51		Group||Project|||Start By||||End By		Group||Project|||Start By||End By
52		1||||A|||3/22/21|||3/23/21		1||A|||3/22/21|||3/23/21
53
54		#VALUE!		#VALUE!
55		2|||||B2|||||3/22/21|||||3/25/21		2|||B2|||3/22/21|||3/25/21
56		3||C|||3/23/21||||3/25/21		3||C|||3/23/21||3/25/21
57		4||||D|||3/23/21||||3/23/21		4||D|||3/23/21||3/23/21
58		5||||E1|||||3/23/21||||3/26/21		5||E1|||3/23/21||3/26/21
59		5||||E2||||||3/24/21|||3/29/21		5||E2||3/24/21|||3/29/21
60		5||||||E3||3/25/21|||3/30/21		5||E3||3/25/21|||3/30/21
61
62		Delimiter is pipe
63		Formula in cell D51☛ =ATRIM(B51:B60, "||")
64
ATRIM

Cell Formulas
Range	Formula
F4:H11	F4	=ATRIM(B4:D11,)
C8	C8	=NA()
B14	B14	=AFORMULATEXT(F4)
D19:D26	D19	=ATRIM(B19:B26, "/")
B21,B54,B37	B21	=1+"A"
B29	B29	=AFORMULATEXT(D19)
D34:D43	D34	=ATRIM(B34:B43, "|")
B46,B63	B46	=AFORMULATEXT(D34)
D51:D60	D51	=ATRIM(B51:B60, "||")
Dynamic array formulas.

 

[schardt679]

ATRIM has been updated to include the optional argument syntax. The Delimiter argument is now truly optional. If omitted or left blank, a space will be used.


ATRIM combines TRIM and CLEAN to remove all extra delimiter characters in text strings (like TRIM for spaces) and remove all non-printing characters listed in CLEAN.

Thanks to XLambda's ATRIM for the inspiration.
Similar to other Excel text functions, values are returned as text.
ASCII delete characters (CHAR 127) ⇨ empty strings and non-breaking spaces (CHAR 160) ⇨ spaces (CHAR 32).
Calls IFBLANK.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ATRIM(Array☛ range to trim & clean, [Delimiter]☛ separator; =" ") ⁂[]=optional; =default; =omit

Syntax: ATRIM(Array,[Delimiter])

Arguments:
Array: Required. Array to remove extra delimiters and non-printing characters.
[Delimiter]: Optional. Value separator(s); ignored☛ space. Can be multiple separators but the row/ column count must match array.

=LAMBDA(Array,[Delimiter],
      LET(Arr, Array,          Sp, " ",          Del, IFBLANK(Delimiter, Sp),
             C_127, CHAR(127),          C_160, CHAR(160),          No_C127, SUBSTITUTE(CLEAN(Arr), C_127, ""),
             No_C160, SUBSTITUTE(No_C127, C_160, Sp),          DelToC127, SUBSTITUTE(No_C160, Del, C_127),
             SpToC160, SUBSTITUTE(DelToC127, Sp, C_160),          C127ToSp, TRIM(SUBSTITUTE(SpToC160, C_127, Sp)),
             Result, SUBSTITUTE(C127ToSp, Sp, Del),          Return, SUBSTITUTE(Result, C_160, Sp),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		ATRIM
2
3		Original Data				Delimiter		Result
4		Product	Sales	Date				Product	Sales	Date
5		Pepper Deseeder	19	03/22/21				Pepper Deseeder	19	03/22/21
6		Knife Set	4	03/22/21				Knife Set	4	03/22/21
7		Cutting Board	7	03/22/21				Cutting Board	7	44277
8		Pepper Deseeder	#N/A	03/23/21				Pepper Deseeder	#N/A	03/23/21
9		Knife Set	28	03/23/21				Knife Set	28	44278
10		Cutting Board	16	03/23/21				Cutting Board	16	03/23/21
11		Pepper Deseeder		03/24/21				Pepper Deseeder		44279
12
13		Delimiter is space
14		Formula in cell H4☛ =ATRIM(B4:D11)
15
ATRIM

Cell Formulas
Range	Formula
H4:J11	H4	=ATRIM(B4:D11)
C8	C8	=NA()
B14	B14	=AFORMULATEXT(H4)
Dynamic array formulas.