AKEEP

=AKEEP(a,k,d)

a
1D vertical array, for 2D arrays we can use ATEXTJOIN
k
string, chars to be kept
d
string, delimiter, ignored or null string "", no delimiter

keeps only certain chars of an array, all the others are removed and replaced with a delimiter or not

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
AKEEP array keep, keeps only certain chars of an array, all the others are removed and replaced with a delimiter or not. calls AREMOVE
Other functions used on minisheet ASPLIT , AUNQSRT , AFILTER , AAGGREGATE
Excel Formula:
=LAMBDA(a,k,d,
    LET(x,CONCAT(AREMOVE(a,k,)),l,LEN(x),
       m,SORT(UNIQUE(UNICODE(MID(x,SEQUENCE(l),1)))),
       AREMOVE(a,CONCAT(UNICHAR(m)),d)
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJ
1sample=AKEEP(A2:A3,C3,)
2akjhda786875, %&/jnlsdj(kjbkl)cg vhjk45678keep only 77%g7
3hghg354678· %&/hghj_>Z3s54dr6fty8 6545DRFTGHJg7H%gg7%gH
4
5extract unique dates for each row in ascending order
6dfg><rd23/3/21-uy21/3/21g-·$%&23/03/21CVrt%&
705/04/21ftgy$%&02/04/21gkh-<·$%&2/4/21
8
9=AKEEP(A6:A7,T_CHARS(1,,"/"),"-")=ASPLIT(A10#,"-")=AUNQSRT(AUNQSRT(C10#,),1)
1023/3/21-21/3/21-23/03/2144278442764427821-03-2123-03-21
1105/04/21-02/04/21-2/4/2144291442884428802-04-2105-04-21
12
13sum the values found on each row
1425.465jdlk·$%DF-56.34hg$%TYT-ghdWERT"·$%%&46.5
15-18.6JHGytr&%$-JHGFuye&%$45.17JHG-JKHuytr(&$
16
17=AKEEP(A14:A15,T_CHARS(1,,"-."),"\")=ASPLIT(A18#,"\")
1825.465\-56.34\-\46.525.465-56.34-46.5
19-18.6\-\45.17\--18.6-45.17-
20=AFILTER(C18#,1,)=AAGGREGATE(C21#,5,)
21other lambdas used in the minisheet25.465-56.3446.515.625
22ASPLIT-18.645.1726.57
23AUNQSRT
24AFILTER
25AAGGREGATE
26
AKEEP post
Cell Formulas
RangeFormula
E1,G20,C20,A17,C17,A9,G9,C9E1=FORMULATEXT(E2)
E2:E3E2=AKEEP(A2:A3,C3,)
A10:A11A10=AKEEP(A6:A7,T_CHARS(1,,"/"),"-")
C10:E11C10=ASPLIT(A10#,"-")
G10:H11G10=AUNQSRT(AUNQSRT(C10#,),1)
A18:A19A18=AKEEP(A14:A15,T_CHARS(1,,"-."),"\")
C18:F19C18=ASPLIT(A18#,"\")
C21:E22C21=AFILTER(C18#,1,)
G21:G22G21=AAGGREGATE(C21#,5,)
Dynamic array formulas.
 
Upvote 0

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
New function non recursive, same functionality, same arguments, now named: ar,ka (array and keep array) arguments, can be 2D arrays, d, delimiter argument can be omitted. Calls last AREMOVE.
Keeps only certain chars of an array, all the others are removed and replaced with a delimiter or not
Other functions on mini-sheet last T_CHARS
Excel Formula:
=LAMBDA(a,ka,[d],LET(k,TEXTJOIN("",,ka),x,TEXTJOIN("",,AREMOVE(a,k)),y,AREMOVE(a,x,d),IFERROR(--y,y)))
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOP
1keeping bunch of charskeeping bunch of chars, other chars,
2no delimiter replacement, d,omittedreplaced with a string delimiter
3sample arraysample array
4abCD123xyXY456Note: function consistent withxy23abcd43FG52ghjt32XY
5efEF567ghGH890numbers, whenever the case54ATdfDFat9128VBNvbn13
6keep digitscheck
7=AKEEP(A4:B5,T_CHARS(1))=ISNUMBER(A8#)keep numbers
8123456TRUETRUEd,"|"
9567890TRUETRUE=AKEEP(H4:I5,T_CHARS(1),"|")
10keep lower letters23|4352|32
11=AKEEP(A4:B5,T_CHARS(,-1))keep letters54|9128|13
12abxy=AKEEP(A4:B5,T_CHARS(,2))any "bunch" of char not kept, is replaced only with one delimiter
13efghabCDxyXY
14keep capital lettersefEFghGHkeep letters
15=AKEEP(A4:B5,T_CHARS(,1))d,"-|-"
16CDXY=AKEEP(H4:I5,T_CHARS(,2),"-|-")
17EFGHxy-|-abcd-|-FGghjt-|-XY
18other functionsATdfDFatVBNvbn
19ka,keep array as arraylast T_CHARSkeep lower letterskeep upper letters
20=AKEEP(H4:I5,T_CHARS(,-1),"-|-")=AKEEP(H4:I5,T_CHARS(,1),"-|-")
21ka,arrayxy-|-abcdghjtFGXY
22AG=AKEEP(A4:B5,A22:B28)df-|-atvbnAT-|-DFVBN
23BHCD123xyXYany "bunch" of char removed, is replaced only with the string delimiter
24CXEFGH890
25DY
26Ex
27Fy
28123890
29
new AKEEP post 1
Cell Formulas
RangeFormula
A7,D7,D22,H20,K20,H16,A15,D12,A11,H9A7=FORMULATEXT(A8)
A8:B9A8=AKEEP(A4:B5,T_CHARS(1))
D8:E9D8=ISNUMBER(A8#)
H10:I11H10=AKEEP(H4:I5,T_CHARS(1),"|")
A12:B13A12=AKEEP(A4:B5,T_CHARS(,-1))
D13:E14D13=AKEEP(A4:B5,T_CHARS(,2))
A16:B17A16=AKEEP(A4:B5,T_CHARS(,1))
H17:I18H17=AKEEP(H4:I5,T_CHARS(,2),"-|-")
H21:I22H21=AKEEP(H4:I5,T_CHARS(,-1),"-|-")
K21:L22K21=AKEEP(H4:I5,T_CHARS(,1),"-|-")
D23:E24D23=AKEEP(A4:B5,A22:B28)
Dynamic array formulas.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
LAMBDA 1.1.2.xlsx
ABCDEFG
1Task 1: extracting numbers, (negative/positive decimal numbers)
2(keeping digits + representative chars
3used in number constructions, like "-"/"+" minus/plus sign, or ".",decimal point)
4sample
5dVy,-24.75,ABC,13.5, -.35,XyzW,+23.54other functions
689.45Abc,yT-123,456xYaB,+76.432,bGhlast T_CHARS
7ATEXTSPLIT
8T_CHARS with extra chars "-+."
9=T_CHARS(1,,"-+.")
101234567890-+.
11
12=AKEEP(A5:A6,A10,"/")=ATEXTSPLIT(A13#,"/")
13-24.75/13.5/-.35/+23.54-24.7513.5-0.3523.54
1489.45/-123/456/+76.43289.45-12345676.432
15
16single cellCheck
17=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")=ISNUMBER(C13#)
18-24.75/13.5/-.35/+23.54TRUETRUETRUETRUE
1989.45/-123/456/+76.432TRUETRUETRUETRUE
20
21if negative nr. format is between "()"TRUETRUETRUETRUE
22sampleTRUETRUETRUETRUE
23dVy,(24.75),ABC,13.5, (.35),XyzW,+23.54=ISNUMBER(C27#)
2489.45Abc,yT(123),456xYaB,+76.432,bGhCheck
25
26=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")=ATEXTSPLIT(A27#,"/")
27(24.75)/13.5/(.35)/23.54-24.7513.5-0.3523.54
2889.45/(123)/456/76.43289.45-12345676.432
29
new AKEEP post 2
Cell Formulas
RangeFormula
A9,A26,C26,A17,C17,A12,C12A9=FORMULATEXT(A10)
A10A10=T_CHARS(1,,"-+.")
A13:A14A13=AKEEP(A5:A6,A10,"/")
C13:F14,C27:F28C13=ATEXTSPLIT(A13#,"/")
A18:A19A18=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")
C18:F19C18=ISNUMBER(C13#)
C21:F22C21=ISNUMBER(C27#)
C23C23=FORMULATEXT(C21)
A27:A28A27=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")
Dynamic array formulas.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
LAMBDA 1.1.2.xlsx
ABCDEFG
1Task 1: extracting numbers, (negative/positive decimal numbers)
2(keeping digits + representative chars
3used in number constructions, like "-"/"+" minus/plus sign, or ".",decimal point)
4sample
5dVy,-24.75,ABC,13.5, -.35,XyzW,+23.54other functions
689.45Abc,yT-123,456xYaB,+76.432,bGhlast T_CHARS
7ATEXTSPLIT
8T_CHARS with extra chars "-+."
9=T_CHARS(1,,"-+.")
101234567890-+.
11
12=AKEEP(A5:A6,A10,"/")=ATEXTSPLIT(A13#,"/")
13-24.75/13.5/-.35/+23.54-24.7513.5-0.3523.54
1489.45/-123/456/+76.43289.45-12345676.432
15
16single cellCheck
17=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")=ISNUMBER(C13#)
18-24.75/13.5/-.35/+23.54TRUETRUETRUETRUE
1989.45/-123/456/+76.432TRUETRUETRUETRUE
20
21if negative nr. format is between "()"TRUETRUETRUETRUE
22sampleTRUETRUETRUETRUE
23dVy,(24.75),ABC,13.5, (.35),XyzW,+23.54=ISNUMBER(C27#)
2489.45Abc,yT(123),456xYaB,+76.432,bGhCheck
25
26=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")=ATEXTSPLIT(A27#,"/")
27(24.75)/13.5/(.35)/23.54-24.7513.5-0.3523.54
2889.45/(123)/456/76.43289.45-12345676.432
29
new AKEEP post 2
Cell Formulas
RangeFormula
A9,A26,C26,A17,C17,A12,C12A9=FORMULATEXT(A10)
A10A10=T_CHARS(1,,"-+.")
A13:A14A13=AKEEP(A5:A6,A10,"/")
C13:F14,C27:F28C13=ATEXTSPLIT(A13#,"/")
A18:A19A18=AKEEP(A5:A6,T_CHARS(1,,"-+."),"/")
C18:F19C18=ISNUMBER(C13#)
C21:F22C21=ISNUMBER(C27#)
C23C23=FORMULATEXT(C21)
A27:A28A27=AKEEP(A23:A24,T_CHARS(1,,"(.)"),"/")
Dynamic array formulas.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
LAMBDA 1.1.2.xlsx
ABCDEFGH
1Task 2: extracting numbers with commas as thousands separators format
2what if numbers have thounsands separators ","
3and same "," is used as delimiterother functions
4samplelast T_CHARS
5Cdxy,-1,345.23,xyAB,123,465.35zxWQ,(.2)ATEXTSPLIT
6rtY(1,023,456),nmMN23,575.20kL,+45,108.05AREMOVE
7Note: If we remove the ",", numbers with thousands separators will not be affected
8=AREMOVE(A5:A6,",")
9Cdxy-1345.23xyAB123465.35zxWQ(.2)
10rtY(1023456)nmMN23575.20kL+45108.05
11after we remove ",", we can reuse it as delimiter
12=AKEEP(A9#,T_CHARS(1,,"().-+"),",")=ATEXTSPLIT(A13#,",")
13-1345.23,123465.35,(.2)-1345.23123465.35-0.2
14(1023456),23575.20,+45108.05-102345623575.245108.05
15
16single cell formula
17=AKEEP(AREMOVE(A5:A6,","),T_CHARS(1,,"().-+"),",")=ATEXTSPLIT(A18#,",")
18-1345.23,123465.35,(.2)-1345.23123465.35-0.2
19(1023456),23575.20,+45108.05-102345623575.245108.05
20
21Check both arrays
22=AND(ISNUMBER(C13#),ISNUMBER(C18#))
23TRUE
24
25Dates complex scenarios extraction will follow with the help of the new AREPLACE, AOCREPLACE functions !!
26
new AKEEP post 3
Cell Formulas
RangeFormula
A8,C22,A17,C17,A12,C12A8=FORMULATEXT(A9)
A9:A10A9=AREMOVE(A5:A6,",")
A13:A14A13=AKEEP(A9#,T_CHARS(1,,"().-+"),",")
C13:E14,C18:E19C13=ATEXTSPLIT(A13#,",")
A18:A19A18=AKEEP(AREMOVE(A5:A6,","),T_CHARS(1,,"().-+"),",")
C23C23=AND(ISNUMBER(C13#),ISNUMBER(C18#))
Dynamic array formulas.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
LAMBDA 1.1.2.xlsx
ABCDEFGHIJ
1Task 3: Single cell formula to extract numbers from array using combo AREMOVE/AKEEP
2number formats that formula should handle:
3- positive,negative,decimal,negative (123), thousands separators (1,234,567.34) ,scientific using exponent E (1.23E+5, -1.23E-5)
4sample
5Aa+2,321,123.25tY,rT(25,678.35)vwQSvX-754.65nME-(cD),wS-1.23E-5pLstep 3: splitting for ","
6E-(dGh-K]-1,100,101.03,xZ5.75E+4fRfHj(2.34E-6)bNM,zSD234.345qWE=ATEXTSPLIT(A15#,",")
72321123.3-25678.35
8step 1: removing thousand separators-754.65E-()-1.23E-05
9=AREMOVE(A5:B6,",")E-(--110010157500
10Aa+2321123.25tYrT(25678.35)vwQSvX-754.65nME-(cD)wS-1.23E-5pL-2.34E-06234.345E
11E-(dGh-K]-1100101.03xZ5.75E+4fRfHj(2.34E-6)bNMzSD234.345qWE
12step 4: filtering numbers only
13step 2: keeping digits and chars used in number representations ".+-()E", replacing others with ","=AFUSBYROW(E7#,1)
14=AKEEP(A10#,T_CHARS(1,,".+-()E"),",")2321123.3-25678.35
15+2321123.25,(25678.35)-754.65,E-(,),-1.23E-5-754.65-0.0000123
16E-(,-,-1100101.03,5.75E+4(2.34E-6),234.345,E-110010157500
17-2.34E-06234.345
18
19functions on mini-sheetstep 5: resizing
20AKEEP=ARESIZE(E14#,2,4)
21AREMOVE2321123.3-25678.35-754.65-1.23E-05
22ATEXTSPLIT-110010157500-2.34E-06234.345
23AFUSBYROWS
24ARESIZEsingle cell
25T_CHARS=ARESIZE(AFUSBYROW(ATEXTSPLIT(AKEEP(AREMOVE(A5:B6,","),T_CHARS(1,,".+-()E"),","),","),1),2,4)
262321123.3-25678.35-754.65-1.23E-05
27-110010157500-2.34E-06234.345
28
new AKEEP post 4
Cell Formulas
RangeFormula
E6,E20,A14,E13,A9E6=FORMULATEXT(E7)
E7:H10E7=ATEXTSPLIT(A15#,",")
A10:B11A10=AREMOVE(A5:B6,",")
E14:F17E14=AFUSBYROW(E7#,1)
A15:B16A15=AKEEP(A10#,T_CHARS(1,,".+-()E"),",")
E21:H22E21=ARESIZE(E14#,2,4)
B25B25=FORMULATEXT(E26)
E26:H27E26=ARESIZE(AFUSBYROW(ATEXTSPLIT(AKEEP(AREMOVE(A5:B6,","),T_CHARS(1,,".+-()E"),","),","),1),2,4)
Dynamic array formulas.
 

Forum statistics

Threads
1,141,575
Messages
5,707,176
Members
421,495
Latest member
jono_oh

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
Top