STRIM(text,space_removal_type)
text
Required. Specifies the cell that will be used by the function for space removal
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

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

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
528
Office Version
  1. 365
Platform
  1. Windows
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).

Excel Formula:
=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
ABCDEFGH
1
2Spacesremove leadingremove trailingremove alltrim
3nonego home nowgo home nowgo home nowgohomenowgo home now
4leading go home nowgo home now go home nowgohomenowgo home now
5trailinggo home now go home now go home nowgohomenowgo home now
6middlego home nowgo home nowgo home nowgohomenowgo home now
7middlego home nowgo home nowgo home nowgohomenowgo home now
8leading/middle go home nowgo home now go home nowgohomenowgo home now
9trailing/middlego home now go home now go home nowgohomenowgo home now
10leading/trailing go home now go home now go home nowgohomenowgo home now
11
Sheet1
Cell Formulas
RangeFormula
D3:D10D3=STRIM($C3,"leading")
E3:E10E3=STRIM($C3,"trailing")
F3:F10F3=STRIM($C3,"all")
G3:G10G3=STRIM($C3,"trim")



Please feel free to share any thoughts or suggestions.

This work is based on the discussion at the following thread:
 
Upvote 0
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
Excel Formula:
=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:
Excel Formula:
=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
ABCDEFGHIJKLMNOP
1text format in C9: 3 groups of 3 letters separated by 3 sp ; leading 3sp and trailing 3sp=> tot length=21
2
3ka,omitted=>TRIMka,1=>rem.leadingka,-1=>rem.trailingka,2=> rem.middleka,3=>rem.all
4=SPTRIM(C5:C11)=SPTRIM(C5:C11,1)=SPTRIM(C5:C11,-1)=SPTRIM(C5:C11,2)=SPTRIM(C5:C11,3)
5empty string      
6blank
7err#N/A#N/A#N/A#N/A#N/A#N/A
8err#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
9logicTRUETRUETRUETRUETRUETRUE
10number23.423.423.423.423.423.4
11text abc def ghi abc def ghiabc def ghi abc def ghi abc def ghi abcdefghi
12
13=LEN(C11)=LEN(E11)=LEN(G11)=LEN(I11)=LEN(K11)=LEN(M11)
14checking len21111818179
15
16checking distrib.3 leading9 lett6x3=186x3=189 lett9 lett
173 trailing2 sptot 18tot 182 sptot 9
182 interv *3=6tot 113 leading
199 letters3 trailing
20tot 21tot 17
21
22ka,{1,2}=>rem. leading,middleka,{-1,2}=>rem. trailing, middleka,{-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/ANote: Data types other than text are kept
27#DIV/0!#DIV/0!#DIV/0!=ISLOGICAL(K28)
28TRUETRUETRUETRUE
2923.423.423.4TRUE
30abc def ghi abc def ghiabc def ghi=ISNUMBER(K29)
31
32=LEN(E30)=LEN(H30)=LEN(K30)
33checking len141415
34
35checking distrib.9 lett9 lett5x3=15
362 sp2 sptot 15
373 trailing3 leading
38tot 14tot 14
39
Sheet2
Cell Formulas
RangeFormula
E4,G4,I4,K4,M4,K32,H32,E32,M27,K23,E23,H23,M13,K13,I13,G13,E13,C13E4=FORMULATEXT(E5)
C5C5=""
E5:E11E5=SPTRIM(C5:C11)
G5:G11G5=SPTRIM(C5:C11,1)
I5:I11I5=SPTRIM(C5:C11,-1)
K5:K11K5=SPTRIM(C5:C11,2)
M5:M11M5=SPTRIM(C5:C11,3)
C7C7=NA()
C8C8=1/0
C14,K33,H33,E33,M14,K14,I14,G14,E14C14=LEN(C11)
E24:E30E24=SPTRIM(C5:C11,{1,2})
H24:H30H24=SPTRIM(C5:C11,{-1,2})
K24:K30K24=SPTRIM(C5:C11,{-1,1})
M28M28=ISLOGICAL(K28)
M29M29=ISNUMBER(K29)
M30M30=FORMULATEXT(M29)
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNO
1
2SPTRIMST
3#CALC!#CALC!#CALC!
4STRIMpost #11 SPTRIM
5References enetered individuallyReferences enetered individually
6Spacesremove leadingremove trailingremove alltrimSpacesremove leadingremove trailingremove alltrim
7nonego home nowgo home nowgo home nowgohomenowgo home nownonego home nowgo home nowgo home nowgo home nowgo home now
8leading go home nowgo home now go home nowgohomenowgo home nowleading go home nowgo home now go home now go home nowgo home now
9trailinggo home now go home now go home nowgohomenowgo home nowtrailinggo home now go home now go home nowgo home now go home now
10middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home nowgo home nowgo home nowgo home nowgo home now
11middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home nowgo home nowgo home nowgo home nowgo home now
12leading/middle go home nowgo home now go home nowgohomenowgo home nowleading/middle go home nowgo home now go home now go home nowgo home now
13trailing/middlego home now go home now go home nowgohomenowgo home nowtrailing/middlego home now go home now go home nowgo home now go home now
14leading/trailing go home now go home now go home nowgohomenowgo home nowleading/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)17541754175417541754
16(logical)TRUE#N/A#N/A#N/A#N/A(logical)TRUETRUETRUETRUETRUE
17(logical)FALSE#N/A#N/A#N/A#N/A(logical)FALSEFALSEFALSEFALSEFALSE
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
23References enetered as range for spillReferences enetered as range for spill
24Spacesremove leadingremove trailingremove alltrimSpacesremove leadingremove trailingremove alltrim
25nonego home nowgo home nowgo home nowgohomenowgo home nownonego home nowgo home nowgo home nowgo home nowgo home now
26leading go home nowgo home now go home nowgohomenowgo home nowleading go home nowgo home now go home now go home nowgo home now
27trailinggo home now go home now go home nowgohomenowgo home nowtrailinggo home now go home now go home nowgo home now go home now
28middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home nowgo home nowgo home nowgo home nowgo home now
29middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home nowgo home nowgo home nowgo home nowgo home now
30leading/middle go home nowgo home now go home nowgohomenowgo home nowleading/middle go home nowgo home now go home now go home nowgo home now
31trailing/middlego home now go home now go home nowgohomenowgo home nowtrailing/middlego home now go home now go home nowgo home now go home now
32leading/trailing go home now go home now go home nowgohomenowgo home nowleading/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)17541754175417541754
34(logical)TRUE#N/A#N/A#N/A#N/A(logical)TRUETRUETRUETRUETRUE
35(logical)FALSE#N/A#N/A#N/A#N/A(logical)FALSEFALSEFALSEFALSEFALSE
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
39wrong type and/or number of parameter(s)wrong type and/or number of parameter(s)
40Spacesremove leadingremove trailingremove alltrimSpacesremove leadingremove trailingremove alltrim
41nonego home now#N/A#N/A#N/A#N/Anonego home nowgo home nowgo home nowgohomenowgohomenow
42leading go home now#N/A#N/A#N/A#N/Aleading go home nowgohomenowgohomenow#DIV/0!gohomenow
43trailinggo home now #N/A#N/A#N/A#N/Atrailinggo home now gohomenow#REF!gohomenowgohomenow
44
45
46STRIM helper functions
47IO#CALC!
48JAN#CALC!
49JAO#CALC!
50JAP#CALC!
51PLSA#CALC!
52
Sheet1
Cell Formulas
RangeFormula
B3B3=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)))))
I3I3=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) ) )
J3J3=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:D19D7=STRIM(C7,"leading")
E7:E19E7=STRIM(C7,"trailing")
F7:F19F7=STRIM(C7,"all")
G7:G19G7=STRIM(C7,"trim")
D20D20=STRIM("","leading")
E20E20=STRIM("","trailing")
F20F20=STRIM("","all")
G20G20=STRIM("","trim")
D21:G21D21=STRIM("",{"leading","all"})
K7:K19K7=SPTRIM(J7,1)
L7:L19L7=SPTRIM(J7,-1)
M7:M19M7=SPTRIM(J7,2)
N7:N19N7=SPTRIM(J7,)
K20K20=SPTRIM("",1)
L20L20=SPTRIM("",-1)
M20M20=SPTRIM("",2)
N20N20=SPTRIM("",)
K21,K41K21=SPTRIM(J21,{1,2})
L21L21=SPTRIM(J21,{1,2})
M21M21=SPTRIM(J21,{1,2})
N21N21=SPTRIM(J21,{1,2})
D25:D37D25=STRIM(C25:C37,"leading")
E25:E37E25=STRIM(C25:C37,"trailing")
F25:F37F25=STRIM(C25:C37,"all")
G25:G37G25=STRIM(C25:C37,"trim")
K25:K37K25=SPTRIM(J25:J37,1)
L25:L37L25=SPTRIM(J25:J37,-1)
M25:M37M25=SPTRIM(J25:J37,2)
N25:N37N25=SPTRIM(J25:J37,)
D41D41=STRIM(C41,{1,2})
E41E41=STRIM(C41,{1,2,-1})
F41F41=STRIM(C41,{1,3})
G41G41=STRIM(C41,{-1,-2})
D42D42=STRIM(C42,{"a",2})
E42E42=STRIM(C42,{1,TRUE})
F42F42=STRIM(C42,{#DIV/0!,3})
G42G42=STRIM(C42,{"b",FALSE})
D43D43=STRIM(C43,TRUE)
E43E43=STRIM(C43,#REF!)
F43F43=STRIM(C43,"c")
G43G43=STRIM(C43,FALSE)
L41L41=SPTRIM(J41,{1,2,-1})
M41M41=SPTRIM(J41,{1,3})
N41N41=SPTRIM(J41,{-1,-2})
K42K42=SPTRIM(J42,{"a",2})
L42L42=SPTRIM(J42,{1,TRUE})
M42M42=SPTRIM(J42,{#DIV/0!,3})
N42N42=SPTRIM(J42,{"b",FALSE})
K43K43=SPTRIM(J43,TRUE)
L43L43=SPTRIM(J43,#REF!)
M43M43=SPTRIM(J43,"c")
N43N43=SPTRIM(J43,FALSE)
C47C47=LAMBDA(parameter,ISOMITTED(parameter))
C48C48=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))
C49C49=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))
C50C50=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))
C51C51=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.
 

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top