STRIM

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
R

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:
www.mrexcel.com

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 www.mrexcel.com
 
Upvote 0
Updated code:
1) adds a third optional parameter to control the output for entries that are not text
2) enables spill support

STRIM
VBA Code: 
=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)
VBA Code: 
=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)
VBA Code: 
=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)
VBA Code: 
=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])
VBA Code: 
=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
ABCDEFGHI
1
2#CALC!
3
4References enetered individually
5Spacesremove leadingremove trailingremove alltrim
6nonego home nowgo home nowgo home nowgohomenowgo home now
7leading go home nowgo home now go home nowgohomenowgo home now
8trailinggo home now go home now go home nowgohomenowgo home now
9middlego home nowgo home nowgo home nowgohomenowgo home now
10middlego home nowgo home nowgo home nowgohomenowgo home now
11leading/middle go home nowgo home now go home nowgohomenowgo home now
12trailing/middlego home now go home now go home nowgohomenowgo home now
13leading/trailing go home now go home now go home nowgohomenowgo home now
14
15
16References enetered as range for spill
17Spacesremove leadingremove trailingremove alltrim
18nonego home nowgo home nowgo home nowgohomenowgo home now
19leading go home nowgo home now go home nowgohomenowgo home now
20trailinggo home now go home now go home nowgohomenowgo home now
21middlego home nowgo home nowgo home nowgohomenowgo home now
22middlego home nowgo home nowgo home nowgohomenowgo home now
23leading/middle go home nowgo home now go home nowgohomenowgo home now
24trailing/middlego home now go home now go home nowgohomenowgo home now
25leading/trailing go home now go home now go home nowgohomenowgo home now
26
27
Sheet1
Cell Formulas
RangeFormula
B2B2=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:E13E6=STRIM(D6,"leading")
F6:F13F6=STRIM(D6,"trailing")
G6:G13G6=STRIM(D6,"all")
H6:H13H6=STRIM(D6,"trim")
E18:E25E18=STRIM(D18:D25,"leading")
F18:F25F18=STRIM(D18:D25,"trailing")
G18:G25G18=STRIM(D18:D25,"all")
H18:H25H18=STRIM(D18:D25,"trim")
Dynamic array formulas.
 
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)
VBA Code: 
=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)
VBA Code: 
=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
VBA Code: 
=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.
 
still have name error

1676955152725.png
 
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.
 
Update for the code:

STRIM
VBA Code: 
=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)
VBA Code: 
=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)
VBA Code: 
=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)
VBA Code: 
=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])
VBA Code: 
=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])
VBA Code: 
=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])
VBA Code: 
=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)
VBA Code: 
=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)
VBA Code: 
=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)
VBA Code: 
=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)
VBA Code: 
=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])
VBA Code: 
=LAMBDA(reference,SUM(IFERROR(T1F0(reference),0)))

STRIM.xlsx
ABCDEFGHI
1
2#CALC!
3
4References enetered individually
5Spacesremove leadingremove trailingremove alltrim
6nonego home nowgo home nowgo home nowgohomenowgo home now
7leading go home nowgo home now go home nowgohomenowgo home now
8trailinggo home now go home now go home nowgohomenowgo home now
9middlego home nowgo home nowgo home nowgohomenowgo home now
10middlego home nowgo home nowgo home nowgohomenowgo home now
11leading/middle go home nowgo home now go home nowgohomenowgo home now
12trailing/middlego home now go home now go home nowgohomenowgo home now
13leading/trailing go home now go home now go home nowgohomenowgo home now
14
15
16References enetered as range for spill
17Spacesremove leadingremove trailingremove alltrim
18nonego home nowgo home nowgo home nowgohomenowgo home now
19leading go home nowgo home now go home nowgohomenowgo home now
20trailinggo home now go home now go home nowgohomenowgo home now
21middlego home nowgo home nowgo home nowgohomenowgo home now
22middlego home nowgo home nowgo home nowgohomenowgo home now
23leading/middle go home nowgo home now go home nowgohomenowgo home now
24trailing/middlego home now go home now go home nowgohomenowgo home now
25leading/trailing go home now go home now go home nowgohomenowgo home now
26
Sheet1
Cell Formulas
RangeFormula
B2B2=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:E13E6=STRIM(D6,"leading")
F6:F13F6=STRIM(D6,"trailing")
G6:G13G6=STRIM(D6,"all")
H6:H13H6=STRIM(D6,"trim")
E18:E25E18=STRIM(D18:D25,"leading")
F18:F25F18=STRIM(D18:D25,"trailing")
G18:G25G18=STRIM(D18:D25,"all")
H18:H25H18=STRIM(D18:D25,"trim")
Dynamic array formulas.
 
Simplified the code to require fewer helper functions:

STRIM
VBA Code: 
=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)
VBA Code: 
=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])
VBA Code: 
=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])
VBA Code: 
=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])
VBA Code: 
=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))

IO (shortened form of ISOMITTED)
VBA Code: 
=LAMBDA(parameter,ISOMITTED(parameter))
 
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.
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, SUBSTITUTE(t, " ", ""))
    )
)
Book1.xlsx
ABCDEFGHIJKLMNOP
12k,-1 => rem. trailing sp.k,1 =>rem. leading sp.k,omitted=> TRIMk,2 => rem. all sp.
13t=SPTRIM(B14:C17,-1)=SPTRIM(B14:C17,1)=SPTRIM(B14:C17)=SPTRIM(B14:C17,2)
14 abc def abc def abc def abc defabc def abc def abc defabc defabcdefabcdef
15 abc def abc def abc def abc defabc def abc def abc defabc defabcdefabcdef
16 abc def abc def abc def abc defabc def abc def abc defabc defabcdefabcdef
17 abc def abc def abc def abc defabc def abc def abc defabc defabcdefabcdef
18
19all strings are identical :=LEN(E14#)=LEN(H14#)=LEN(K14#)=LEN(N14#)
203 leading, 3 middle and 121212127766
213 trailing spaces121212127766
22 => 5*3=15 chars121212127766
23121212127766
24checking LEN
25=LEN(B14:C17)
261515
271515
281515
291515
30
Sheet1
Cell Formulas
RangeFormula
E13,B25,N19,K19,H19,E19,K13,N13,H13E13=FORMULATEXT(E14)
E14:F17E14=SPTRIM(B14:C17,-1)
H14:I17H14=SPTRIM(B14:C17,1)
K14:L17K14=SPTRIM(B14:C17)
N14:O17N14=SPTRIM(B14:C17,2)
E20:F23,N20:O23,K20:L23,H20:I23E20=LEN(E14#)
B26:C29B26=LEN(B14:C17)
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNOP
1
2#CALC!#CALC!
3STRIMSPTRIM
4References enetered individuallyReferences enetered individually
5Spacesremove leadingremove trailingremove alltrimSpacesremove leadingremove trailingremove alltrim
6nonego home nowgo home nowgo home nowgohomenowgo home nownonego home nowgo home nowgo home nowgohomenowgo home now
7leading go home nowgo home now go home nowgohomenowgo home nowleading go home nowgo home now go home nowgohomenowgo home now
8trailinggo home now go home now go home nowgohomenowgo home nowtrailinggo home now go home now go home nowgohomenowgo home now
9middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home nowgo home nowgo home nowgohomenowgo home now
10middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home nowgo home nowgo home nowgohomenowgo home now
11leading/middle go home nowgo home now go home nowgohomenowgo home nowleading/middle go home nowgo home now go home nowgohomenowgo home now
12trailing/middlego home now go home now go home nowgohomenowgo home nowtrailing/middlego home now go home now go home nowgohomenowgo home now
13leading/trailing go home now go home now go home nowgohomenowgo home nowleading/trailing go home now go home now go home nowgohomenowgo home now
14(number)1754#N/A#N/A#N/A#N/A(number)17541754175417541754
15(logical)TRUE#N/A#N/A#N/A#N/A(logical)TRUETRUETRUETRUETRUE
16(logical)FALSE#N/A#N/A#N/A#N/A(logical)FALSEFALSEFALSEFALSEFALSE
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
22References enetered as range for spillReferences enetered as range for spill
23Spacesremove leadingremove trailingremove alltrimSpacesremove leadingremove trailingremove alltrim
24nonego home nowgo home nowgo home nowgohomenowgo home nownonego home now#DIV/0!#DIV/0!gohomenowgo home now
25leading go home nowgo home now go home nowgohomenowgo home nowleading go home now#DIV/0!#DIV/0!gohomenowgo home now
26trailinggo home now go home now go home nowgohomenowgo home nowtrailinggo home now #DIV/0!#DIV/0!gohomenowgo home now
27middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home now#DIV/0!#DIV/0!gohomenowgo home now
28middlego home nowgo home nowgo home nowgohomenowgo home nowmiddlego home now#DIV/0!#DIV/0!gohomenowgo home now
29leading/middle go home nowgo home now go home nowgohomenowgo home nowleading/middle go home now#DIV/0!#DIV/0!gohomenowgo home now
30trailing/middlego home now go home now go home nowgohomenowgo home nowtrailing/middlego home now #DIV/0!#DIV/0!gohomenowgo home now
31leading/trailing go home now go home now go home nowgohomenowgo home nowleading/trailing go home now #DIV/0!#DIV/0!gohomenowgo home now
32(number)1754#N/A#N/A#N/A#N/A(number)1754#DIV/0!#DIV/0!17541754
33(logical)TRUE#N/A#N/A#N/A#N/A(logical)TRUE#DIV/0!#DIV/0!TRUETRUE
34(logical)FALSE#N/A#N/A#N/A#N/A(logical)FALSE#DIV/0!#DIV/0!FALSEFALSE
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
38STRIM helper functionsReferences enetered as range for spill
39IO#CALC!Spacesremove leadingremove trailingremove alltrim
40JAN#CALC!nonego home nowgo home nowgo home nowgohomenowgo home now
41JAO#CALC!leading go home now go home now go home nowgohomenowgo home now
42JAP#CALC!trailinggo home now go home now go home now gohomenowgo home now
43PLSA#CALC!middlego home nowgo home nowgo home nowgohomenowgo home now
44middlego home nowgo home nowgo home nowgohomenowgo home now
45leading/middle go home now go home now go home nowgohomenowgo home now
46trailing/middlego home now go home now go home now gohomenowgo home now
47leading/trailing go home now go home now go home now gohomenowgo home now
48(number)17541754175417541754
49(logical)TRUETRUETRUETRUETRUE
50(logical)FALSEFALSEFALSEFALSEFALSE
51(blank)
52
Sheet1
Cell Formulas
RangeFormula
B2B2=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)))))
I2I2=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:D18D6=STRIM(C6,"leading")
E6:E18E6=STRIM(C6,"trailing")
F6:F18F6=STRIM(C6,"all")
G6:G18G6=STRIM(C6,"trim")
D19D19=STRIM("","leading")
E19E19=STRIM("","trailing")
F19F19=STRIM("","all")
G19G19=STRIM("","trim")
D20:G20D20=STRIM("",{"leading","all"})
K6:K18K6=SPTRIM(J6,1)
L6:L18L6=SPTRIM(J6,-1)
M6:M18M6=SPTRIM(J6,2)
N6:N18N6=SPTRIM(J6,)
K19K19=SPTRIM("",1)
L19L19=SPTRIM("",-1)
M19M19=SPTRIM("",2)
N19N19=SPTRIM("",)
K20K20=SPTRIM(J20,{1,2})
L20L20=SPTRIM(J20,{1,2})
M20M20=SPTRIM(J20,{1,2})
N20:O20N20=SPTRIM(J20,{1,2})
D24:D36D24=STRIM(C24:C36,"leading")
E24:E36E24=STRIM(C24:C36,"trailing")
F24:F36F24=STRIM(C24:C36,"all")
G24:G36G24=STRIM(C24:C36,"trim")
K24:K36K24=SPTRIM(J24:J36,1)
L24:L36L24=SPTRIM(J24:J36,-1)
M24:M36M24=SPTRIM(J24:J36,2)
N24:N36N24=SPTRIM(J24:J36,)
K40:K51K40=SPTRIM(J40:J51,1)
L40:L51L40=SPTRIM(J40:J51,-1)
M40:M51M40=SPTRIM(J40:J51,2)
N40:N51N40=SPTRIM(J40:J51,)
C39C39=LAMBDA(parameter,ISOMITTED(parameter))
C40C40=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))
C41C41=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))
C42C42=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))
C43C43=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.
 
You must log in or register to reply here.

Forum statistics

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

Share this page

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