STRIM

[Rnkhch]

STRIM is a complete "space" removal solution to conveniently manage leading, trailing, or all spaces within a string

STRIM stands for "space trim". STRIM allows for conveniently specifying what type of spaces to remove form a string. The choices are: leading spaces, trailing spaces, all spaces, and TRIM-style space removal (where all spaces except one space in between words are removed).

=LAMBDA(text,space_removal_type,
   SWITCH(space_removal_type,
       "leading",
          LET(a,LEN(text),
             (RIGHT(text,a-(MATCH(TRUE,INDEX(CODE(MID(text,ROW(INDIRECT("1:"&a)),1))<>32,),0)-1)))),
       "trailing",
          LET(a,LEN(text),
             (LEFT(text,a-(a-AGGREGATE(14,6,ROW(INDIRECT("1:"&a))/(CODE(MID(text,ROW(INDIRECT("1:"&a)),1))<>32),1))))),
       "all",
          SUBSTITUTE(text," ",""),
       "trim",
          TRIM(text)
    )
)

Blank power workbook1
	A	B	C	D	E	F	G	H
1
2		Spaces		remove leading	remove trailing	remove all	trim
3		none	go home now	go home now	go home now	gohomenow	go home now
4		leading	go home now	go home now	go home now	gohomenow	go home now
5		trailing	go home now	go home now	go home now	gohomenow	go home now
6		middle	go home now	go home now	go home now	gohomenow	go home now
7		middle	go home now	go home now	go home now	gohomenow	go home now
8		leading/middle	go home now	go home now	go home now	gohomenow	go home now
9		trailing/middle	go home now	go home now	go home now	gohomenow	go home now
10		leading/trailing	go home now	go home now	go home now	gohomenow	go home now
11
Sheet1

Cell Formulas
Range	Formula
D3:D10	D3	=STRIM($C3,"leading")
E3:E10	E3	=STRIM($C3,"trailing")
F3:F10	F3	=STRIM($C3,"all")
G3:G10	G3	=STRIM($C3,"trim")

Please feel free to share any thoughts or suggestions.

This work is based on the discussion at the following thread:

How to count the total number of leading and trailing spaces in cells?

Hello, I'm trying to come up with a way to count leading and trailing spaces in cells. I have seen posts discussing ways of trimming such spaces, but I didn't see anything about counting them. (The reason I need this is that it will help me identify the source software from which some of my...

www.mrexcel.com

 

[Rnkhch]

Updated code:
1) adds a third optional parameter to control the output for entries that are not text
2) enables spill support

STRIM

=LAMBDA(text,space_removal_type,[if_not_text],
   LET(
      s,PLSA(space_removal_type,{"leading","trailing","all","trim"}),
      IF(
         OR(ISARRAY(if_not_text),ISERROR(s)),
         NA(),
         LET(
            i,IF(IO(if_not_text),NA(),if_not_text),
            t,IF(ISONLYTEXT(text),text,NA()),
            IF(
               ISTEXT(t),
               SWITCH(s,
                  "leading",MAP(t,LAMBDA(x,LET(y,LEN(x),RIGHT(x,y-(MATCH(TRUE,INDEX(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32,),0)-1))))),
                  "trailing",MAP(t,LAMBDA(x,LET(y,LEN(x),LEFT(x,y-(y-AGGREGATE(14,6,ROW(INDIRECT("1:"&y))/(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32),1)))))),
                  "all",MAP(t,LAMBDA(x,SUBSTITUTE(x," ",""))),
                  "trim",MAP(t,LAMBDA(x,TRIM(x)))),
               i
             )
          )
      )
   )
)

The following helper functions are also used in the code. Full code and descriptions are included in my "Blank power workbook" template, which you can download through the link in my signature below:

PLSA (Returns the parameter if it is a single entry [not blank or error] matching the allowed_entries and #N/A otherwise)

=LAMBDA(parameter,allowed_entries,
   JAN(IF(
      OR(ISARRAY(parameter),ISERRORS(parameter),ISBLANKS(parameter)),
      NA(),
      IFERROR(IF(MATCH(parameter,allowed_entries,0),parameter),NA())
      )
   )
)

ISARRAY (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise)

=LAMBDA([reference],[output],LET(r,IFERROR(reference,""),o,PLSL(output),m,IF(IO(reference),FALSE,IF(RBYC(r)>1,TRUE,FALSE)),JAO(m,o)))

IO (shortened form of ISOMITTED)

=LAMBDA(parameter,ISOMITTED(parameter))

ISONLYTEXT (Checks whether a value is strictly text and returns TRUE or FALSE [avoids recognition of formula blanks as text by ISTEXT])

=LAMBDA(reference,[output],
   LET(
      r,IFERROR(reference,FALSE),
      o,PLSL(output),
      m,IF(ISTEXT(r)*NOT(r=""),TRUE,FALSE),
      IF(ISERROR(o),NA(),IF(m,OT(o),OF(o)))
   )
)

STRIM.xlsx
	A	B	C	D	E	F	G	H	I
1
2		#CALC!
3
4					References enetered individually
5			Spaces		remove leading	remove trailing	remove all	trim
6			none	go home now	go home now	go home now	gohomenow	go home now
7			leading	go home now	go home now	go home now	gohomenow	go home now
8			trailing	go home now	go home now	go home now	gohomenow	go home now
9			middle	go home now	go home now	go home now	gohomenow	go home now
10			middle	go home now	go home now	go home now	gohomenow	go home now
11			leading/middle	go home now	go home now	go home now	gohomenow	go home now
12			trailing/middle	go home now	go home now	go home now	gohomenow	go home now
13			leading/trailing	go home now	go home now	go home now	gohomenow	go home now
14
15
16					References enetered as range for spill
17			Spaces		remove leading	remove trailing	remove all	trim
18			none	go home now	go home now	go home now	gohomenow	go home now
19			leading	go home now	go home now	go home now	gohomenow	go home now
20			trailing	go home now	go home now	go home now	gohomenow	go home now
21			middle	go home now	go home now	go home now	gohomenow	go home now
22			middle	go home now	go home now	go home now	gohomenow	go home now
23			leading/middle	go home now	go home now	go home now	gohomenow	go home now
24			trailing/middle	go home now	go home now	go home now	gohomenow	go home now
25			leading/trailing	go home now	go home now	go home now	gohomenow	go home now
26
27
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(text,space_removal_type,[if_not_text],LET(s,PLSA(space_removal_type,{"leading","trailing","all","trim"}),IF(OR(ISARRAY(if_not_text),ISERROR(s)),NA(),LET(i,IF(IO(if_not_text),NA(),if_not_text),t,IF(ISONLYTEXT(text),text,NA()),IF(ISTEXT(t),SWITCH(s,"leading",MAP(t,LAMBDA(x,LET(y,LEN(x),RIGHT(x,y-(MATCH(TRUE,INDEX(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32,),0)-1))))),"trailing",MAP(t,LAMBDA(x,LET(y,LEN(x),LEFT(x,y-(y-AGGREGATE(14,6,ROW(INDIRECT("1:"&y))/(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32),1)))))),"all",MAP(t,LAMBDA(x,SUBSTITUTE(x," ",""))),"trim",MAP(t,LAMBDA(x,TRIM(x)))),i)))))
E6:E13	E6	=STRIM(D6,"leading")
F6:F13	F6	=STRIM(D6,"trailing")
G6:G13	G6	=STRIM(D6,"all")
H6:H13	H6	=STRIM(D6,"trim")
E18:E25	E18	=STRIM(D18:D25,"leading")
F18:F25	F18	=STRIM(D18:D25,"trailing")
G18:G25	G18	=STRIM(D18:D25,"all")
H18:H25	H18	=STRIM(D18:D25,"trim")
Dynamic array formulas.

 

[steven88]

what iserros and isblanks output name

 

[Rnkhch]

Oh, those are additional functions I made:

ISERRORS(reference,unique_value_if_error,[all],[unique_value_if_not_error])
Returns a single value_if_error if the refrence contains at least one error and the reference itself otherwise (if "all" is TRUE, value_if_error is returned if all reference elements are errors)

=LAMBDA(reference,[all],[output],IF(IO(reference),FALSE,LET(a,PLSL(all),o,PLSL(output),s,ST1F0(ISERROR(reference)),m,SWITCH(a,0,IF(s,TRUE,FALSE),1,IF(s=RBYC(reference),TRUE,FALSE)),JAO(m,o))))

ISBLANKS(reference,[all],[output])
Returns TRUE if the reference contains at least one blank and FALSE otherwise (if "all" is TRUE, TRUE is returned if all reference elements are blanks)

=LAMBDA(reference,[all],[output],IF(IO(reference),NA(),LET(a,PLSL(all),b,ST1F0(reference=""),o,PLSL(output),s,SWITCH(a,0,IF(b,TRUE,FALSE),1,IF(b=RBYC(reference),TRUE,FALSE)),IF(s,OT(o),OF(o)))))

PLSL([parameter])
Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise

=LAMBDA([parameter],IF(IO(parameter),0,IF(ISARRAY(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))

These are all in my template that you can download and use at the link below (in my signature). They help with keeping the main function's code less verbose.

 

[steven88]

still have name error

 

[Rnkhch]

Let me double check and I'll get back to you tomorrow.

It is working on my template, so likely I missed to write one of the helper functions in my post. In the meantime, feel free to download the template.

 

[Rnkhch]

Update for the code:

STRIM

=LAMBDA(text,space_removal_type,[if_not_text],
   LET(
      s,PLSA(space_removal_type,{"leading","trailing","all","trim"}),
      IF(
         OR(ISARRAY(if_not_text),ISERROR(s)),
         NA(),
         LET(
            i,IF(IO(if_not_text),NA(),if_not_text),
            t,IF(ISONLYTEXT(text),text,NA()),
            IF(
               ISTEXT(t),
               SWITCH(s,
                  "leading",MAP(t,LAMBDA(x,LET(y,LEN(x),RIGHT(x,y-(MATCH(TRUE,INDEX(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32,),0)-1))))),
                  "trailing",MAP(t,LAMBDA(x,LET(y,LEN(x),LEFT(x,y-(y-AGGREGATE(14,6,ROW(INDIRECT("1:"&y))/(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32),1)))))),
                  "all",MAP(t,LAMBDA(x,SUBSTITUTE(x," ",""))),
                  "trim",MAP(t,LAMBDA(x,TRIM(x)))
               ),
               i
            )
         )
      )
   )
)

PLSA (stands for "parameter limit, single, allowed_entries")
(Returns the parameter if it is a single entry (not blank or error) matching the allowed_entries and #N/A otherwise)

=LAMBDA(parameter,allowed_entries,INDEX(IF(OR(ISARRAY(parameter),ISERRORS(parameter),ISBLANKS(parameter)),NA(),IFERROR(IF(MATCH(parameter,allowed_entries,0),parameter),NA())),1))

ISARRAY (Returns TRUE if the reference is an array [i.e. is not a single element or is not omitted] and FALSE otherwise)

=LAMBDA([reference],[output],LET(r,IFERROR(IF(reference="","",reference),""),o,PLSL(output),m,IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE)),IF(m,OT(o),OF(o))))

IO (shortened form of ISOMITTED)

=LAMBDA(parameter,ISOMITTED(parameter))

ISONLYTEXT(Checks whether a value is strictly text and returns TRUE or FALSE [avoids recognition of formula blanks as text by ISTEXT])

=LAMBDA(reference,[output],LET(r,IFERROR(reference,FALSE),o,PLSL(output),m,IF(ISTEXT(r)*NOT(r=""),TRUE,FALSE),IF(ISERROR(o),NA(),IF(m,OT(o),OF(o)))))

ISERRORS (Returns TRUE if the reference contains at least one error and FALSE otherwise [if "all" is TRUE, TRUE is returned if all reference elements are errors])

=LAMBDA(reference,[all],[output],IF(IO(reference),FALSE,LET(a,PLSL(all),o,PLSL(output),s,ST1F0(ISERROR(reference)),m,SWITCH(a,0,IF(s,TRUE,FALSE),1,IF(s=ROWS(reference)*COLUMNS(reference),TRUE,FALSE)),IF(m,OT(o),OF(o)))))

ISBLANKS (Returns TRUE if the reference contains at least one blank and FALSE otherwise [if "all" is TRUE, TRUE is returned if all reference elements are blanks])

=LAMBDA(reference,[all],[output],IF(IO(reference),NA(),LET(a,PLSL(all),b,ST1F0(reference=""),o,PLSL(output),s,SWITCH(a,0,IF(b,TRUE,FALSE),1,IF(b=ROWS(reference)*COLUMNS(reference),TRUE,FALSE)),IF(s,OT(o),OF(o)))))

PLSL (stands for "parameter limit, single, logical")
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or omitted; and #N/A otherwise)

=LAMBDA([parameter],IF(IO(parameter),0,IF(ISARRAY(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))

OT (Returns "TRUE" if the reference is TRUE, "1" if the reference is FALSE, and #N/A otherwise)

=LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,1,TRUE),NA())))

OF (Returns "FALSE" if the reference is TRUE, "0" if the reference is FALSE, and #N/A otherwise)

=LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,0,FALSE),NA())))

T1F0 (Returns 1 if the reference is TRUE and 0 if the reference is FALSE)

=LAMBDA(reference,[analysis_type],LET(a,PLSL(analysis_type),b,IF(ISNUMBER(reference)+ISLOGICAL(reference),reference,SWITCH(a,0,NA(),1,0)),IF(b,1,0)))

ST1F0 (Returns the sum of all TRUE (1) and FALSE (0) elements of the reference [counting all elements that cannot be logically evaluated as 0])

=LAMBDA(reference,SUM(IFERROR(T1F0(reference),0)))

STRIM.xlsx
	A	B	C	D	E	F	G	H	I
1
2		#CALC!
3
4					References enetered individually
5			Spaces		remove leading	remove trailing	remove all	trim
6			none	go home now	go home now	go home now	gohomenow	go home now
7			leading	go home now	go home now	go home now	gohomenow	go home now
8			trailing	go home now	go home now	go home now	gohomenow	go home now
9			middle	go home now	go home now	go home now	gohomenow	go home now
10			middle	go home now	go home now	go home now	gohomenow	go home now
11			leading/middle	go home now	go home now	go home now	gohomenow	go home now
12			trailing/middle	go home now	go home now	go home now	gohomenow	go home now
13			leading/trailing	go home now	go home now	go home now	gohomenow	go home now
14
15
16					References enetered as range for spill
17			Spaces		remove leading	remove trailing	remove all	trim
18			none	go home now	go home now	go home now	gohomenow	go home now
19			leading	go home now	go home now	go home now	gohomenow	go home now
20			trailing	go home now	go home now	go home now	gohomenow	go home now
21			middle	go home now	go home now	go home now	gohomenow	go home now
22			middle	go home now	go home now	go home now	gohomenow	go home now
23			leading/middle	go home now	go home now	go home now	gohomenow	go home now
24			trailing/middle	go home now	go home now	go home now	gohomenow	go home now
25			leading/trailing	go home now	go home now	go home now	gohomenow	go home now
26
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(text,space_removal_type,[if_not_text],LET(s,PLSA(space_removal_type,{"leading","trailing","all","trim"}),IF(OR(ISARRAY(if_not_text),ISERROR(s)),NA(),LET(i,IF(IO(if_not_text),NA(),if_not_text),t,IF(ISONLYTEXT(text),text,NA()),IF(ISTEXT(t),SWITCH(s,"leading",MAP(t,LAMBDA(x,LET(y,LEN(x),RIGHT(x,y-(MATCH(TRUE,INDEX(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32,),0)-1))))),"trailing",MAP(t,LAMBDA(x,LET(y,LEN(x),LEFT(x,y-(y-AGGREGATE(14,6,ROW(INDIRECT("1:"&y))/(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32),1)))))),"all",MAP(t,LAMBDA(x,SUBSTITUTE(x," ",""))),"trim",MAP(t,LAMBDA(x,TRIM(x)))),i)))))
E6:E13	E6	=STRIM(D6,"leading")
F6:F13	F6	=STRIM(D6,"trailing")
G6:G13	G6	=STRIM(D6,"all")
H6:H13	H6	=STRIM(D6,"trim")
E18:E25	E18	=STRIM(D18:D25,"leading")
F18:F25	F18	=STRIM(D18:D25,"trailing")
G18:G25	G18	=STRIM(D18:D25,"all")
H18:H25	H18	=STRIM(D18:D25,"trim")
Dynamic array formulas.

 

[Rnkhch]

Simplified the code to require fewer helper functions:

STRIM

=LAMBDA(text,space_removal_type,[if_not_text],
   LET(
      s,PLS(space_removal_type),
      IF(
         OR(JAN(if_not_text),ISERROR(s)),
         NA(),
         LET(
            i,IF(IO(if_not_text),NA(),if_not_text),
            t,IF(ISTEXT(text)*NOT(text=""),text,NA()),
            IF(
               ISTEXT(t),
               SWITCH(s,
                  "leading",MAP(t,LAMBDA(x,LET(y,LEN(x),RIGHT(x,y-(MATCH(TRUE,INDEX(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32,),0)-1))))),
                  "trailing",MAP(t,LAMBDA(x,LET(y,LEN(x),LEFT(x,y-(y-AGGREGATE(14,6,ROW(INDIRECT("1:"&y))/(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32),1)))))),
                  "all",MAP(t,LAMBDA(x,SUBSTITUTE(x," ",""))),
                  "trim",MAP(t,LAMBDA(x,TRIM(x)))
               ),
               i
            )
         )
      )
   )
)

PLS (stands for parameter limit, single)
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)

=LAMBDA(parameter,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),1))

JAN (Helper function: Returns TRUE if the reference is an array [i.e. is not a single element or is not omitted] and FALSE otherwise [simplified form of ISARRAY])

=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))

JAO (Helper function: Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])

=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))

JAP (Helper function: Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])

=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))

IO (shortened form of ISOMITTED)

=LAMBDA(parameter,ISOMITTED(parameter))

 

[Xlambda]

Hi, I do not know if I am missing some functionality, here is a function that does it all, no lambda helper functions needed, no volatile INDIRECT or calling any other helper function.
SPTRIM(t,[k])
t: any array of strings
[k]: if omitted=>TRIM ; if -1 remove trailing sp. ; if 1 remove leading sp. ; if 2 remove all sp.

=LAMBDA(t, [k],
    LET(
        b, TRIM(t),
        l, LEN(t),
        a, LEN(TEXTBEFORE(t, LEFT(b))),
        c, LEN(TEXTAFTER(t, RIGHT(b), -1)),
        SWITCH(k, -1, LEFT(t, l - c), 1, RIGHT(t, l - a), 0, b, SUBSTITUTE(t, " ", ""))
    )
)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
12					k,-1 => rem. trailing sp.			k,1 =>rem. leading sp.			k,omitted=> TRIM			k,2 => rem. all sp.
13		t			=SPTRIM(B14:C17,-1)			=SPTRIM(B14:C17,1)			=SPTRIM(B14:C17)			=SPTRIM(B14:C17,2)
14		abc def	abc def		abc def	abc def		abc def	abc def		abc def	abc def		abcdef	abcdef
15		abc def	abc def		abc def	abc def		abc def	abc def		abc def	abc def		abcdef	abcdef
16		abc def	abc def		abc def	abc def		abc def	abc def		abc def	abc def		abcdef	abcdef
17		abc def	abc def		abc def	abc def		abc def	abc def		abc def	abc def		abcdef	abcdef
18
19		all strings are identical :			=LEN(E14#)			=LEN(H14#)			=LEN(K14#)			=LEN(N14#)
20		3 leading, 3 middle and			12	12		12	12		7	7		6	6
21		3 trailing spaces			12	12		12	12		7	7		6	6
22		=> 5*3=15 chars			12	12		12	12		7	7		6	6
23					12	12		12	12		7	7		6	6
24		checking LEN
25		=LEN(B14:C17)
26		15	15
27		15	15
28		15	15
29		15	15
30
Sheet1

Cell Formulas
Range	Formula
E13,B25,N19,K19,H19,E19,K13,N13,H13	E13	=FORMULATEXT(E14)
E14:F17	E14	=SPTRIM(B14:C17,-1)
H14:I17	H14	=SPTRIM(B14:C17,1)
K14:L17	K14	=SPTRIM(B14:C17)
N14:O17	N14	=SPTRIM(B14:C17,2)
E20:F23,N20:O23,K20:L23,H20:I23	E20	=LEN(E14#)
B26:C29	B26	=LEN(B14:C17)
Dynamic array formulas.

 

[Rnkhch]

Hi @Xlambda,

Thank you so much, and great to hear from you! You're absolute number one Your function's core is simpler, more efficient, and shorter.

The helper functions I added were primarily intended to make the function behave exactly like a native function meaning when the wrong input is entered, or when the wrong argument is entered for "space_removal_type", or when more than one argument is entered for "space_removal_type", then the function returns a single #N/A (blue highlight in the attached XL2BB). The STRIM is on the left and SPTRIM is on the right, with the exact same inputs for comparison (see yellow highlights). It seems that if the input range contains an error, SPTRIM doesn't work for "leading" and "trailing". (at the bottom section, I've removed the row that contains the error input) Also STRIM has the extra "if_not_text" parameter.

I will work on applying the SPTRIM core to STRIM and see what I can get.

STRIM.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		#CALC!							#CALC!
3		STRIM							SPTRIM
4				References enetered individually							References enetered individually
5		Spaces		remove leading	remove trailing	remove all	trim		Spaces		remove leading	remove trailing	remove all	trim
6		none	go home now	go home now	go home now	gohomenow	go home now		none	go home now	go home now	go home now	gohomenow	go home now
7		leading	go home now	go home now	go home now	gohomenow	go home now		leading	go home now	go home now	go home now	gohomenow	go home now
8		trailing	go home now	go home now	go home now	gohomenow	go home now		trailing	go home now	go home now	go home now	gohomenow	go home now
9		middle	go home now	go home now	go home now	gohomenow	go home now		middle	go home now	go home now	go home now	gohomenow	go home now
10		middle	go home now	go home now	go home now	gohomenow	go home now		middle	go home now	go home now	go home now	gohomenow	go home now
11		leading/middle	go home now	go home now	go home now	gohomenow	go home now		leading/middle	go home now	go home now	go home now	gohomenow	go home now
12		trailing/middle	go home now	go home now	go home now	gohomenow	go home now		trailing/middle	go home now	go home now	go home now	gohomenow	go home now
13		leading/trailing	go home now	go home now	go home now	gohomenow	go home now		leading/trailing	go home now	go home now	go home now	gohomenow	go home now
14		(number)	1754	#N/A	#N/A	#N/A	#N/A		(number)	1754	1754	1754	1754	1754
15		(logical)	TRUE	#N/A	#N/A	#N/A	#N/A		(logical)	TRUE	TRUE	TRUE	TRUE	TRUE
16		(logical)	FALSE	#N/A	#N/A	#N/A	#N/A		(logical)	FALSE	FALSE	FALSE	FALSE	FALSE
17		(error)	#DIV/0!	#N/A	#N/A	#N/A	#N/A		(error)	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
18		(blank)		#N/A	#N/A	#N/A	#N/A		(blank)
19		(blank entered as "")	""	#N/A	#N/A	#N/A	#N/A		(blank entered as "")	""
20		(more than one parameter)		#N/A	#N/A	#N/A	#N/A		(more than one parameter)	go home now	#SPILL!	#SPILL!	#SPILL!	go home now	gohomenow
21
22				References enetered as range for spill							References enetered as range for spill
23		Spaces		remove leading	remove trailing	remove all	trim		Spaces		remove leading	remove trailing	remove all	trim
24		none	go home now	go home now	go home now	gohomenow	go home now		none	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
25		leading	go home now	go home now	go home now	gohomenow	go home now		leading	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
26		trailing	go home now	go home now	go home now	gohomenow	go home now		trailing	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
27		middle	go home now	go home now	go home now	gohomenow	go home now		middle	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
28		middle	go home now	go home now	go home now	gohomenow	go home now		middle	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
29		leading/middle	go home now	go home now	go home now	gohomenow	go home now		leading/middle	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
30		trailing/middle	go home now	go home now	go home now	gohomenow	go home now		trailing/middle	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
31		leading/trailing	go home now	go home now	go home now	gohomenow	go home now		leading/trailing	go home now	#DIV/0!	#DIV/0!	gohomenow	go home now
32		(number)	1754	#N/A	#N/A	#N/A	#N/A		(number)	1754	#DIV/0!	#DIV/0!	1754	1754
33		(logical)	TRUE	#N/A	#N/A	#N/A	#N/A		(logical)	TRUE	#DIV/0!	#DIV/0!	TRUE	TRUE
34		(logical)	FALSE	#N/A	#N/A	#N/A	#N/A		(logical)	FALSE	#DIV/0!	#DIV/0!	FALSE	FALSE
35		(error)	#DIV/0!	#N/A	#N/A	#N/A	#N/A		(error)	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
36		(blank)		#N/A	#N/A	#N/A	#N/A		(blank)		#DIV/0!	#DIV/0!
37
38		STRIM helper functions									References enetered as range for spill
39		IO	#CALC!						Spaces		remove leading	remove trailing	remove all	trim
40		JAN	#CALC!						none	go home now	go home now	go home now	gohomenow	go home now
41		JAO	#CALC!						leading	go home now	go home now	go home now	gohomenow	go home now
42		JAP	#CALC!						trailing	go home now	go home now	go home now	gohomenow	go home now
43		PLSA	#CALC!						middle	go home now	go home now	go home now	gohomenow	go home now
44									middle	go home now	go home now	go home now	gohomenow	go home now
45									leading/middle	go home now	go home now	go home now	gohomenow	go home now
46									trailing/middle	go home now	go home now	go home now	gohomenow	go home now
47									leading/trailing	go home now	go home now	go home now	gohomenow	go home now
48									(number)	1754	1754	1754	1754	1754
49									(logical)	TRUE	TRUE	TRUE	TRUE	TRUE
50									(logical)	FALSE	FALSE	FALSE	FALSE	FALSE
51									(blank)
52
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(text,space_removal_type,[if_not_text],LET(s,PLSA(space_removal_type,{"leading","trailing","all","trim"}),IF(OR(JAN(if_not_text),ISERROR(s)),NA(),LET(i,IF(IO(if_not_text),NA(),if_not_text),t,IF(ISTEXT(text)*NOT(text=""),text,NA()),IF(ISTEXT(t),SWITCH(s,"leading",MAP(t,LAMBDA(x,LET(y,LEN(x),RIGHT(x,y-(MATCH(TRUE,INDEX(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32,),0)-1))))),"trailing",MAP(t,LAMBDA(x,LET(y,LEN(x),LEFT(x,y-(y-AGGREGATE(14,6,ROW(INDIRECT("1:"&y))/(CODE(MID(x,ROW(INDIRECT("1:"&y)),1))<>32),1)))))),"all",MAP(t,LAMBDA(x,SUBSTITUTE(x," ",""))),"trim",MAP(t,LAMBDA(x,TRIM(x)))),i)))))
I2	I2	=LAMBDA(t,[k],LET(b,TRIM(t),l,LEN(t),a,LEN(TEXTBEFORE(t,LEFT(b))),c,LEN(TEXTAFTER(t,RIGHT(b),-1)),SWITCH(k,-1,LEFT(t,l-c),1,RIGHT(t,l-a),0,b,SUBSTITUTE(t," ",""))))
D6:D18	D6	=STRIM(C6,"leading")
E6:E18	E6	=STRIM(C6,"trailing")
F6:F18	F6	=STRIM(C6,"all")
G6:G18	G6	=STRIM(C6,"trim")
D19	D19	=STRIM("","leading")
E19	E19	=STRIM("","trailing")
F19	F19	=STRIM("","all")
G19	G19	=STRIM("","trim")
D20:G20	D20	=STRIM("",{"leading","all"})
K6:K18	K6	=SPTRIM(J6,1)
L6:L18	L6	=SPTRIM(J6,-1)
M6:M18	M6	=SPTRIM(J6,2)
N6:N18	N6	=SPTRIM(J6,)
K19	K19	=SPTRIM("",1)
L19	L19	=SPTRIM("",-1)
M19	M19	=SPTRIM("",2)
N19	N19	=SPTRIM("",)
K20	K20	=SPTRIM(J20,{1,2})
L20	L20	=SPTRIM(J20,{1,2})
M20	M20	=SPTRIM(J20,{1,2})
N20:O20	N20	=SPTRIM(J20,{1,2})
D24:D36	D24	=STRIM(C24:C36,"leading")
E24:E36	E24	=STRIM(C24:C36,"trailing")
F24:F36	F24	=STRIM(C24:C36,"all")
G24:G36	G24	=STRIM(C24:C36,"trim")
K24:K36	K24	=SPTRIM(J24:J36,1)
L24:L36	L24	=SPTRIM(J24:J36,-1)
M24:M36	M24	=SPTRIM(J24:J36,2)
N24:N36	N24	=SPTRIM(J24:J36,)
K40:K51	K40	=SPTRIM(J40:J51,1)
L40:L51	L40	=SPTRIM(J40:J51,-1)
M40:M51	M40	=SPTRIM(J40:J51,2)
N40:N51	N40	=SPTRIM(J40:J51,)
C39	C39	=LAMBDA(parameter,ISOMITTED(parameter))
C40	C40	=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))
C41	C41	=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))
C42	C42	=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))
C43	C43	=LAMBDA(parameter,allowed_entries,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),IFERROR(IF(MATCH(parameter,allowed_entries,0),parameter),NA())),1))
Dynamic array formulas.