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

 

[Xlambda]

Thanks a lot!
From your argument description:
"space_removal_type: Required. Specifies the type of space removal and takes one of four arguments: "leading", removes leading spaces; "trailing", removes trailing spaces; "all", removes all spaces; "trim", removes all spaces except one space in between words"
No mention of middle spaces plus is mentioning "one of four arguments". That's why I did not do it in the first place. My bad that I did not follow your examples.

Anyhow, here is the function that does it all.
SPTRIM(ar,[ka]) calls ST
ar: any array containing any data type
[ka]: array of arguments or single values
- if ka omitted=> 0 => TRIM op
- if ka 1 => remove leading spaces
- if ka -1 => remove trailing spaces
- if ka 2 => remove middle spaces
- if ka=3 => remove all spaces

=LAMBDA(ar, [ka],
    LET(
        a, IFERROR(ar, 0),
        j, IF(ISOMITTED(ka), 0, ka),
        f, LAMBDA(t, k, MAP(t, LAMBDA(x, IF(ISTEXT(x), ST(x, k), x)))),
        r, REDUCE(a, j, f),
        IF(ar = "", "", r)
    )
)

where ST:

=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, 2, REPT(" ", a) & b & REPT(" ", c), SUBSTITUTE(t, " ", ""))
    )
)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		text format in C9: 3 groups of 3 letters separated by 3 sp ; leading 3sp and trailing 3sp=> tot length=21
2
3					ka,omitted=>TRIM		ka,1=>rem.leading		ka,-1=>rem.trailing		ka,2=> rem.middle		ka,3=>rem.all
4					=SPTRIM(C5:C11)		=SPTRIM(C5:C11,1)		=SPTRIM(C5:C11,-1)		=SPTRIM(C5:C11,2)		=SPTRIM(C5:C11,3)
5		empty string
6		blank
7		err	#N/A		#N/A		#N/A		#N/A		#N/A		#N/A
8		err	#DIV/0!		#DIV/0!		#DIV/0!		#DIV/0!		#DIV/0!		#DIV/0!
9		logic	TRUE		TRUE		TRUE		TRUE		TRUE		TRUE
10		number	23.4		23.4		23.4		23.4		23.4		23.4
11		text	abc def ghi		abc def ghi		abc def ghi		abc def ghi		abc def ghi		abcdefghi
12
13			=LEN(C11)		=LEN(E11)		=LEN(G11)		=LEN(I11)		=LEN(K11)		=LEN(M11)
14		checking len	21		11		18		18		17		9
15
16		checking distrib.	3 leading		9 lett		6x3=18		6x3=18		9 lett		9 lett
17			3 trailing		2 sp		tot 18		tot 18		2 sp		tot 9
18			2 interv *3=6		tot 11						3 leading
19			9 letters								3 trailing
20			tot 21								tot 17
21
22					ka,{1,2}=>rem. leading,middle			ka,{-1,2}=>rem. trailing, middle			ka,{-1,1}=>rem. leading,trailing
23					=SPTRIM(C5:C11,{1,2})			=SPTRIM(C5:C11,{-1,2})			=SPTRIM(C5:C11,{-1,1})
24
25
26					#N/A			#N/A			#N/A		Note: Data types other than text are kept
27					#DIV/0!			#DIV/0!			#DIV/0!		=ISLOGICAL(K28)
28					TRUE			TRUE			TRUE		TRUE
29					23.4			23.4			23.4		TRUE
30					abc def ghi			abc def ghi			abc def ghi		=ISNUMBER(K29)
31
32					=LEN(E30)			=LEN(H30)			=LEN(K30)
33			checking len		14			14			15
34
35			checking distrib.		9 lett			9 lett			5x3=15
36					2 sp			2 sp			tot 15
37					3 trailing			3 leading
38					tot 14			tot 14
39
Sheet2

Cell Formulas
Range	Formula
E4,G4,I4,K4,M4,K32,H32,E32,M27,K23,E23,H23,M13,K13,I13,G13,E13,C13	E4	=FORMULATEXT(E5)
C5	C5	=""
E5:E11	E5	=SPTRIM(C5:C11)
G5:G11	G5	=SPTRIM(C5:C11,1)
I5:I11	I5	=SPTRIM(C5:C11,-1)
K5:K11	K5	=SPTRIM(C5:C11,2)
M5:M11	M5	=SPTRIM(C5:C11,3)
C7	C7	=NA()
C8	C8	=1/0
C14,K33,H33,E33,M14,K14,I14,G14,E14	C14	=LEN(C11)
E24:E30	E24	=SPTRIM(C5:C11,{1,2})
H24:H30	H24	=SPTRIM(C5:C11,{-1,2})
K24:K30	K24	=SPTRIM(C5:C11,{-1,1})
M28	M28	=ISLOGICAL(K28)
M29	M29	=ISNUMBER(K29)
M30	M30	=FORMULATEXT(M29)
Dynamic array formulas.

 

[Rnkhch]

Thanks much @Xlambda

Just got a chance to check back here as I was away all of last week. Your updated function works very well. I'll work on integrating it with STRIM hopefully during the coming week.

The helper function PLSA (and its components) in my STRIM just ensures that if the wrong type and/or number of parameter(s) are entered, the function returns a single #N/A (just like any native Excel function would do). This is to ensure that no erroneous output is generated by accident if the user mistakenly puts in the wrong argument or if the argument comes from another operation embedded in the parameter (the yellow/red highlights for SPTRIM vs. the green highlights for STRIM in the XL2BB below). (I put in entirely wrong arguments in the bottom table I always do that to my own functions to make sure that they crash with any wrong argument.)

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

Cell Formulas
Range	Formula
B3	B3	=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)))))
I3	I3	=LAMBDA(ar,[ka], LET( a, IFERROR(ar, 0), j, IF(ISOMITTED(ka), 0, ka), f, LAMBDA(t,k, MAP(t, LAMBDA(x, IF(ISTEXT(x), ST(x, k), x)))), r, REDUCE(a, j, f), IF(ar = "", "", r) ) )
J3	J3	=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, 2, REPT(" ", a) & b & REPT(" ", c), SUBSTITUTE(t, " ", "")) ) )
D7:D19	D7	=STRIM(C7,"leading")
E7:E19	E7	=STRIM(C7,"trailing")
F7:F19	F7	=STRIM(C7,"all")
G7:G19	G7	=STRIM(C7,"trim")
D20	D20	=STRIM("","leading")
E20	E20	=STRIM("","trailing")
F20	F20	=STRIM("","all")
G20	G20	=STRIM("","trim")
D21:G21	D21	=STRIM("",{"leading","all"})
K7:K19	K7	=SPTRIM(J7,1)
L7:L19	L7	=SPTRIM(J7,-1)
M7:M19	M7	=SPTRIM(J7,2)
N7:N19	N7	=SPTRIM(J7,)
K20	K20	=SPTRIM("",1)
L20	L20	=SPTRIM("",-1)
M20	M20	=SPTRIM("",2)
N20	N20	=SPTRIM("",)
K21,K41	K21	=SPTRIM(J21,{1,2})
L21	L21	=SPTRIM(J21,{1,2})
M21	M21	=SPTRIM(J21,{1,2})
N21	N21	=SPTRIM(J21,{1,2})
D25:D37	D25	=STRIM(C25:C37,"leading")
E25:E37	E25	=STRIM(C25:C37,"trailing")
F25:F37	F25	=STRIM(C25:C37,"all")
G25:G37	G25	=STRIM(C25:C37,"trim")
K25:K37	K25	=SPTRIM(J25:J37,1)
L25:L37	L25	=SPTRIM(J25:J37,-1)
M25:M37	M25	=SPTRIM(J25:J37,2)
N25:N37	N25	=SPTRIM(J25:J37,)
D41	D41	=STRIM(C41,{1,2})
E41	E41	=STRIM(C41,{1,2,-1})
F41	F41	=STRIM(C41,{1,3})
G41	G41	=STRIM(C41,{-1,-2})
D42	D42	=STRIM(C42,{"a",2})
E42	E42	=STRIM(C42,{1,TRUE})
F42	F42	=STRIM(C42,{#DIV/0!,3})
G42	G42	=STRIM(C42,{"b",FALSE})
D43	D43	=STRIM(C43,TRUE)
E43	E43	=STRIM(C43,#REF!)
F43	F43	=STRIM(C43,"c")
G43	G43	=STRIM(C43,FALSE)
L41	L41	=SPTRIM(J41,{1,2,-1})
M41	M41	=SPTRIM(J41,{1,3})
N41	N41	=SPTRIM(J41,{-1,-2})
K42	K42	=SPTRIM(J42,{"a",2})
L42	L42	=SPTRIM(J42,{1,TRUE})
M42	M42	=SPTRIM(J42,{#DIV/0!,3})
N42	N42	=SPTRIM(J42,{"b",FALSE})
K43	K43	=SPTRIM(J43,TRUE)
L43	L43	=SPTRIM(J43,#REF!)
M43	M43	=SPTRIM(J43,"c")
N43	N43	=SPTRIM(J43,FALSE)
C47	C47	=LAMBDA(parameter,ISOMITTED(parameter))
C48	C48	=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))
C49	C49	=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))
C50	C50	=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))
C51	C51	=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.