AREMOVE

=AREMOVE(a,rm,d)
a
1D vertical array, for 2D arrays we can use ATEXTJOIN
rm
chars to be removed
d
string, delimiter, ignored or null string "", no delimiter
!! recursive !! array remove, removes certain chars and replace them with a delimiter or not
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
AREMOVE !! recursive !! array remove, removes certain chars and replace them with a delimiter or not.
Other functions used on minisheet ASPLIT , AUNIQUE , AXLOOKUP , AREPLACE , ATEXTJOIN
Excel Formula: 
=LAMBDA(a,rm,d,
    LET(n,LEN(rm),r,RIGHT(rm,1),
       IF(n=0,ATRIM(a,d,),AREMOVE(SUBSTITUTE(a,r,d),LEFT(rm,n-1),d))
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMN
1sampleextract unique codes=AUNIQUE(E3#,0)
245SDF-dfg, 28KLM-tyubv, 28HJK-tgfhqw=AREMOVE(A2:A4,T_CHARS(,-1,"-,"),"|")=ASPLIT(C3#,"|")45SDF
3523FOYP-gdjhsw, 28KLM-resdrftg45SDF| 28KLM| 28HJK45SDF28KLM28HJK28KLM
428HJK-nvdww, 523WQSD-rxjjytrd523FOYP| 28KLM523FOYP28KLM28HJK
528HJK| 523WQSD28HJK523WQSD523FOYP
6extract unique nrs.523WQSDcodes
7refine chars in T_CHARS selection=AREMOVE(A2:A4,T_CHARS(,2,",-"),",")=ASPLIT(C8#,",")=AUNIQUE(E8#,)282930
8=T_CHARS(1,1,)45, 28, 2845282845434445
90123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ523, 285232828522523524
1028, 52328523523cities
11=AREMOVE(T_CHARS(1,1,),"01234QWERTY",)HagueLondonMadrid
1256789ABCDFGHIJKLMNOPSUVXZcount how many unique colors=AXLOOKUP(I8#,K7:M9,K11:M13,,,)NYLABoston
13remove chars above FG234ed red, HG723lu blueBostonSeoulTokyoBeijing
1446890GJKQKJT,98765432YTRELKJHGSD94yd cyan, LMNre redHague
15FGKERT38502, 2982136BVCXWYTQYW39en green yellow blueTokyo
16replacewith
17=AREMOVE(A14:A15,A12,"")remove digitsred1
1840QT,432YTRE=AREMOVE(C13:C15,T_CHARS(1,,),"")magenta2
19ERT302, 2213WYTQFGed red, HGlu blueblue3
20SDyd cyan, LMNre redbrown4
21other lambdas used in the minisheetYWen green yellow bluegreen5
22ASPLITyellow6
23AUNIQUEreplace colors with nrscyan7
24AXLOOKUP=AREPLACE(C19#,E17:E23,F17:F23)
25AREPLACEFGed 1, HGlu 3
26ATEXTJOINSDyd 7, LMNre 1
27YWen 5 6 3
28ddhh65✌hfg456?u7462?
29=AREMOVE(A28,T_CHARS(1,-1,),"-")extract nrs
30✌-?-?=AREMOVE(LOWER(C25#),T_CHARS(,-1,","),"")=ASPLIT(C31#," ")=COUNT(--AUNIQUE(F31#,))
31 1 3135
32 7 171
33 5 6 3563
34
35extract unique capital letter codes
36TWE678-hgd,GHY2897-ersyWQ YGD hj-53
37VBF32-hthggfhRTY764,435KHtyr
38JHG876-wqer,YGD jhgj-567WQ=ASPLIT(B46#," ")=SORT(AUNIQUE(D39#,))
39TWEGHYWQYGDGHY
40=ATEXTJOIN(A36:B38,,,"|")VBFRTYKHJHG
41TWE678-hgd,GHY2897-ersy|WQ YGD hj-53JHGYGDWQKH
42VBF32-hthg|gfhRTY764,435KHtyrRTY
43JHG876-wqer,YGD jhgj-567|WQTWE
44VBF
45=AREMOVE(A41#,T_CHARS(1,-1,"-,|"),",")=AREPLACE(A46#,","," ")WQ
46TWE,GHY,WQ YGD TWE GHY WQ YGD YGD
47VBF,RTY,KHVBF RTY KH
48JHG,YGD ,WQJHG YGD WQ
49
AREMOVE post
Cell Formulas
RangeFormula
I1,A45:B45,A40,I38,D38,J30,C30,F30,A29,C24,C18,A17,A11,A8,I7,C7,E7,C2,E2I1=FORMULATEXT(I2)
I2:I6I2=AUNIQUE(E3#,0)
C3:C5C3=AREMOVE(A2:A4,T_CHARS(,-1,"-,"),"|")
E3:G5E3=ASPLIT(C3#,"|")
C8:C10C8=AREMOVE(A2:A4,T_CHARS(,2,",-"),",")
E8:G10E8=ASPLIT(C8#,",")
I8:I10I8=AUNIQUE(E8#,)
A9A9=T_CHARS(1,1,)
A12A12=AREMOVE(T_CHARS(1,1,),"01234QWERTY",)
H12H12=FORMULATEXT(I13)
I13:I15I13=AXLOOKUP(I8#,K7:M9,K11:M13,,,)
A18:A19A18=AREMOVE(A14:A15,A12,"")
C19:C21C19=AREMOVE(C13:C15,T_CHARS(1,,),"")
C25:C27C25=AREPLACE(C19#,E17:E23,F17:F23)
A30A30=AREMOVE(A28,T_CHARS(1,-1,),"-")
C31:C33C31=AREMOVE(LOWER(C25#),T_CHARS(,-1,","),"")
F31:H33F31=ASPLIT(C31#," ")
J31J31=COUNT(--AUNIQUE(F31#,))
D39:G41D39=ASPLIT(B46#," ")
I39:I46I39=SORT(AUNIQUE(D39#,))
A41:A43A41=ATEXTJOIN(A36:B38,,,"|")
A46:A48A46=AREMOVE(A41#,T_CHARS(1,-1,"-,|"),",")
B46:B48B46=AREPLACE(A46#,","," ")
Dynamic array formulas.
 
Upvote 0
Brand new function, same functionality, same arguments, non recursive, designed with the !!NEW!! REDUCE lambda helper function. Calls latest ATRIM
Lot of people were sceptic about the utility of REDUCE new function. Here is a reprezentative example, in my opinion, of how can be used, how easy is to replace recursive functions versatility when we need an indefinite nr. of nested calls of a function, without the recursion limitations. Amazing function, my favorite so far.
Designed to remove all occurrences of chosen chars and replace them with delimiters or not, can handle now 2D arrays for "a" and "rm" arguments, string delimiter. For removing/replacing whole words, the new AREPLACE will follow.
Excel Formula: 
=LAMBDA(a,rm,[d],
   LET(t,TEXTJOIN("",,rm),x,MID(t,SEQUENCE(LEN(t)),1),
       y,REDUCE(a,x,LAMBDA(a,i,SUBSTITUTE(a,i,d))),z,ATRIM(y,d),
       IF(ISNUMBER(--z),--z,z)
    )
)
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMN
1removing bunch of charsremoving bunch of chars
2no delimiter replacement, d,omittedwith delimiter replacement
3sample arraysample array
4abCD123xyXY45623abcd4352ghjt32
5efEF567ghGH89054ASDF9128VBN13
6remove digitsremove lettersd,"|"
7=AREMOVE(A4:B5,T_CHARS(1))=AREMOVE(A4:B5,T_CHARS(,2))=AREMOVE(H4:I5,T_CHARS(,2),"|")
8abCDxyXY12345623|4352|32
9efEFghGH56789054|9128|13
10remove small lettersNote: function consistent withany "bunch" of char removed, is replaced only with one delimiter
11=AREMOVE(A4:B5,T_CHARS(,-1))numbers, whenever the cased,"-|-"
12CD123XY456check=AREMOVE(H4:I5,T_CHARS(,2),"-|-")
13EF567GH890=ISNUMBER(D8#)23-|-4352-|-32
14remove capital lettersTRUETRUE54-|-9128-|-13
15=AREMOVE(A4:B5,T_CHARS(,1))TRUETRUEany "bunch" of char removed, is replaced only with the string delimiter
16ab123xy456
17ef567gh890other functions
18last T_CHARS
19
new AREMOVE post 1
Cell Formulas
RangeFormula
A7,D7,H7,A15,D13,H12,A11A7=FORMULATEXT(A8)
A8:B9A8=AREMOVE(A4:B5,T_CHARS(1))
D8:E9D8=AREMOVE(A4:B5,T_CHARS(,2))
H8:I9H8=AREMOVE(H4:I5,T_CHARS(,2),"|")
A12:B13A12=AREMOVE(A4:B5,T_CHARS(,-1))
H13:I14H13=AREMOVE(H4:I5,T_CHARS(,2),"-|-")
D14:E15D14=ISNUMBER(D8#)
A16:B17A16=AREMOVE(A4:B5,T_CHARS(,1))
Dynamic array formulas.
 
Back on Sep 7 (previous post, same thread) I wrote: "how easy is to replace recursive functions versatility when we need an indefinite nr. of nested calls of a function, without the recursion limitations."
Here are simple examples to check if REDUCE has limits as recursion has, other than usual excel limitations, like, for example, max nr. of rows 2^20 or largest allowed nr 9.999…E+307 etc.
Book2.xlsx
ABCDEFGHIJKL
1f(x)=x+1
2in.val.n=REDUCE(A3,SEQUENCE(B3),LAMBDA(x,n,x+1))
3010485761048576
41010485761048586
5=REDUCE(A4,SEQUENCE(,B4),LAMBDA(x,n,x+1))
6
7=REDUCE(A8,SEQUENCE(B8),LAMBDA(x,n,x+1))
801048577#VALUE!
9Not #NUM "recursion"/"iteration" error , #VALUE error due to spreadsheet real estate limitation of 1048576 (2^20) rows/columns,
10Note: Even if a spreadsheet is 16384 clms wide (2^14), inside a formula is no problem using SEQUENCE(,1048576), as seen in C4 formula
11=SEQUENCE(2^20+1)
12#VALUE!
13
14
15f(x)=2*x-1How this sequence looks with SCAN
16=REDUCE(A17,SEQUENCE(B17),LAMBDA(x,n,2*x+1))=SCAN(A17,SEQUENCE(B17),LAMBDA(x,n,2*x+1))
17010238.9885E+3071
18No "recursion"/"iteration" limit3=ROWS(G17#)
1971023
20we are getting closer to15Check
21largest allowed positive number31
229.99999999999999E+30763
23127
24reduce=last value of scan array255
25=INDEX(G17#,1023)511
268.9885E+3071023
272047
284095
298191
3016383
3132767
3265535
33131071
34262143
35524287
Sheet1
Cell Formulas
RangeFormula
C2,C25,I18,G16,B11,C7C2=FORMULATEXT(C3)
C3,C8C3=REDUCE(A3,SEQUENCE(B3),LAMBDA(x,n,x+1))
C4C4=REDUCE(A4,SEQUENCE(,B4),LAMBDA(x,n,x+1))
C5C5=FORMULATEXT(C4)
B12B12=SEQUENCE(2^20+1)
B16B16=FORMULATEXT(C17)
C17C17=REDUCE(A17,SEQUENCE(B17),LAMBDA(x,n,2*x+1))
G17:G1039G17=SCAN(A17,SEQUENCE(B17),LAMBDA(x,n,2*x+1))
I19I19=ROWS(G17#)
C26C26=INDEX(G17#,1023)
Dynamic array formulas.
 
AREMOVE test:
Book2.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1AREMOVE itself put to the "iteration"/"recursion" test.
21600 chars long , single cell string (each char is 2 units long)
3=CONCAT(UNICHAR(SEQUENCE(800,,128000)))
4????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
5
6=LEN(A4)
71600
8
9Now let's create a string ="a"&A4&"b" and try ro remove the inner string (A4) with the function
10="a"&A4&"b"
11a????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????b
12
13=LEN(A11)
141602
15so, new string is created
16
17=AREMOVE(A11,A4,",")
18a,b
19oK
20
Sheet2
Cell Formulas
RangeFormula
A3,A17,A13,A10,A6A3=FORMULATEXT(A4)
A4A4=CONCAT(UNICHAR(SEQUENCE(800,,128000)))
A7,A14A7=LEN(A4)
A11A11="a"&A4&"b"
A18A18=AREMOVE(A11,A4,",")
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,099
Messages
6,170,111
Members
452,302
Latest member
TaMere

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