AREVERSE

=AREVERSE(a)

a
array, 1D vertical array, for 2D arrays we can use ATEXTJOIN

array reverse

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
832
Office Version
  1. 365
Platform
  1. Windows
AREVERSE array reverse, calls ATEXTJOIN. Other lambda in minisheet AKEEP
For a single cell there is a simple recursion lambda, but for an array I choose a non recursive one.
!! recursive !! T_REVERSE(w)=LAMBDA(w,LET(n,LEN(w),r,RIGHT(w,1),IF(n=0,w,r&T_REVERSE(LEFT(w,n-1)))))
Excel Formula:
=LAMBDA(a,
    LET(r,ROWS(a),c,MAX(LEN(a)),s,SEQUENCE(,c),m,MID(a,s,1),
       ATEXTJOIN(INDEX(m,SEQUENCE(r),ABS(s-c-1)),,,)
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJK
1check palindrome sentences=AKEEP(A2:A5,T_CHARS(,2,),)=AREVERSE(B2#)=B2#=D2#
2Murder for a jar of red rumMurderforajarofredrummurderforajarofredruMTRUE
3Borrow or rob?BorroworrobborroworroBTRUE
4Eva, can I see bees in a cave?EvacanIseebeesinacaveevacaniseebeesInacavETRUE
5Yo, banana boy!YobananaboyyobananaboYTRUE
6
7is palindrome=AREVERSE(B8:B13)=B8:B13=D8#=PROPER(LOWER(T_REVERSE(B8)))
8GigiigiGFALSEIgig
91234abcdefggfedcba4321FALSEGfedcba4321
10MadammadaMTRUEMadam
11CivicciviCTRUECivic
12LevelleveLTRUELevel
13RadarradaRTRUERadar
14
15for a single cell recursive lambda
16T_REVERSE(w)=LAMBDA(w,LET(n,LEN(w),r,RIGHT(w,1),IF(n=0,w,r&T_REVERSE(LEFT(w,n-1)))))
17
18other lambdas used
19AKEEP
20
AREVERSE post
Cell Formulas
RangeFormula
B1,F7,D7,H7,F1,D1B1=FORMULATEXT(B2)
B2:B5B2=AKEEP(A2:A5,T_CHARS(,2,),)
D2:D5D2=AREVERSE(B2#)
F2:F5F2=B2#=D2#
D8:D13D8=AREVERSE(B8:B13)
F8:F13F8=B8:B13=D8#
H8:H13H8=PROPER(LOWER(T_REVERSE(B8)))
Dynamic array formulas.
 
Upvote 0
Just saw latest MrExcel YT: Excel Search In Reverse (Jenny) - feat. Tommy Tutone - 2444, (19-Nov-2021) and I thought it's time to update the function using new lambda helper function MAP.
AREVERSE(a,[tn]), brand new function same name, same functionality, can handle 2D arrays, blanks (null strings), has new argument [tn] .
Has a new argument [tn], text/number, which can be omitted.
a: any array, 1D or 2D
[tn]: 0, or omitted, results are kept as text ; 1 or <>0, converts results into numeric values, wherever is possible.
Excel Formula:
=LAMBDA(a,[tn],LET(m,MAP(a,LAMBDA(a,CONCAT(MID(a,SEQUENCE(LEN(a),,LEN(a),-1),1)))),
      IF(a="","",IF(tn,IFERROR(--m,m),m))
      )
)
LAMBDA 1.1.5.xlsx
ABCDEFGHIJKL
1AREVERSE(a,[tn])
2a: array
3[tn]: text/number argument : 0,or omitted,text ; <>0 (1), numeric
4
5Concept is very simple, MID extraction backwards, last to first char, CONCAT the result,
6and MAP does this for each element of "a"
7
8a=MID(A9,SEQUENCE(LEN(A9),,LEN(A9),-1),1)or
9abcdd=MID(A9,LEN(A9)-SEQUENCE(LEN(A9))+1,1)
10c=CONCAT(C9#)d
11bdcbac
12ab
13a
14tn,omitted
15a=AREVERSE(A16:B18)
16abcdefghdcbahgfe
17!"·$%ABCD%$·"!DCBA
18A/b/c/DD/c/b/A
19
20As we know, every text function delivers text, including MID function.
21If will ever need this text to be converted into numeric values, wherever
22is possible, we can use tn=1
23
24Special cases,nr. with leading/trailing 0's, negative numbers,dates in dates format
25
26tn,omitted , all texttn,1 =
27a=AREVERSE(A28:B33)=AREVERSE(A28:B33,1)
2812341230432103214321321
290123027.350321053.72321053.72
30a-b-c-dA_B_C_Dd-c-b-aD_C_B_Ad-c-b-aD_C_B_A
31-123.25-123.2552.321-52.321-52.321-52.321-
32123-)123(-321(321)-321-321
3319-Nov-2119-11-2191544915449154491544¡¡year of reverse date is
342150, not 2050 !!
35last row nr. formatlast row in date format=YEAR(H36)
36445194451920-08-5020-08-502150
37
38Note!! Reversing 1230 when tn=1 will loose the leading 0
39Trailing 0's in reverse order will be lost when tn=1
40Negative numbers with leading "-" or between () , reversed,will not be numbers
41Dates reversed , with tn=1, will be some numbers
42=> crazy format to get negative numbers
43
AREVERSE np1
Cell Formulas
RangeFormula
C8,J35,G27,D27,D15,E10,H9C8=FORMULATEXT(C9)
C9:C12C9=MID(A9,SEQUENCE(LEN(A9),,LEN(A9),-1),1)
H10:H13H10=MID(A9,LEN(A9)-SEQUENCE(LEN(A9))+1,1)
E11E11=CONCAT(C9#)
D16:E18D16=AREVERSE(A16:B18)
D28:E33D28=AREVERSE(A28:B33)
G28:H33G28=AREVERSE(A28:B33,1)
J36J36=YEAR(H36)
Dynamic array formulas.
 
LAMBDA 1.1.5.xlsx
ABCDEFGHIJKLMNOPQRS
1¡¡ This post is for fun ONLY !!What dates are symetric btwn years 1980-2000
2Funny idea: What date is your birthdate in reverse order?sequence of all dates formula
3We can start a trend, to celebrate both dates, every year, the regular one and the reversed one ? =SEQUENCE("31-12-00"-"1-1-80"+1,,"1-1-80")
4symetric dates 1980-2000, single cell
5To find out what date is our birthdate or any date in reverse order with a simple formulas:=LET(x,SEQUENCE("31-12-00"-"1-1-80"+1,,"1-1-80"),y,AREVERSE(x,1),z,FILTER(x,x=y),TEXT(z,"dd-mmm-yy"))
6=DATE(2021,12,31)=AREVERSE(A7,1)=TEXT(C7,"dd-mmm-yyyy")12-Mar-80
731-12-211654417-Apr-194520-Jun-80
828-Sep-80
906-Jan-81
10single cell16-Apr-81
11=TEXT(AREVERSE(DATE(2021,12,31),1),"dd-mmm-yyyy")25-Jul-81
1217-Apr-194502-Nov-81
1310-Feb-82
14How many times we used birthdates of our loved ones, if not ours, as safe codes, pin codes?21-Feb-82
15We use them because are easy to remember, and we don't have to write them down.01-Jun-82
16Also , these are the first combinations that will be checked by bad guys (coworkers ?)09-Sep-82
17With this "encoding" we are safe now ? unless…there is an unless…18-Dec-82
18…the birthdate is symmetric.28-Mar-83
1906-Jul-83
20Also, if we want to "encode" some table date column, we can use this method.14-Oct-83
21Nobody will know when a certain event really took place.?22-Jan-84
22(don't forget to hide the formula)01-May-84
2309-Aug-84
24Let's hope that bad guys don't use Excel !!27-Nov-84
2507-Mar-85
2615-Jun-85
2723-Sep-85
2801-Jan-86
2911-Apr-86
3020-Jul-86
3128-Oct-86
3205-Feb-87
3316-May-87
3403-Sep-87
3512-Dec-87
3621-Mar-88
3729-Jun-88
3807-Oct-88
3915-Jan-89
4025-Apr-89
4103-Aug-89
4211-Nov-89
4319-Feb-90
4409-Jun-90
4517-Sep-90
4626-Dec-90
4705-Apr-91
4814-Jul-91
4922-Oct-91
5030-Jan-92
5109-May-92
5217-Aug-92
5325-Nov-92
5415-Mar-93
5523-Jun-93
5601-Oct-93
5709-Jan-94
5819-Apr-94
5928-Jul-94
6005-Nov-94
6113-Feb-95
6224-May-95
6301-Sep-95
6420-Dec-95
6529-Mar-96
6607-Jul-96
6715-Oct-96
6823-Jan-97
6903-May-97
7011-Aug-97
7119-Nov-97
7227-Feb-98
7307-Jun-98
7425-Sep-98
7503-Jan-99
7613-Apr-99
7722-Jul-99
7830-Oct-99
7907-Feb-00
8017-May-00
8125-Aug-00
8203-Dec-00
83
Fun with dates
Cell Formulas
RangeFormula
I5,A11,A6,C6,E6I5=FORMULATEXT(I6)
I6:I82I6=LET(x,SEQUENCE("31-12-00"-"1-1-80"+1,,"1-1-80"),y,AREVERSE(x,1),z,FILTER(x,x=y),TEXT(z,"dd-mmm-yy"))
A7A7=DATE(2021,12,31)
C7C7=AREVERSE(A7,1)
E7E7=TEXT(C7,"dd-mmm-yyyy")
A12A12=TEXT(AREVERSE(DATE(2021,12,31),1),"dd-mmm-yyyy")
Dynamic array formulas.
 
LAMBDA 1.1.5.xlsx
ABCDEFGHIJKLMNO
1Encoding/Deconding a date set, dealing with leading 0's scenario
2
3sample, dates columndates, nr. formatencoding dates nr. formatencoding, dates formatdecoding the encodingdeconding dates, single cell
4=SEQUENCE(21,,TODAY())=SEQUENCE(21,,TODAY())=AREVERSE(C5#,1)=IF(LEN(E5#)=4,"0"&E5#,E5#)=AREVERSE(IF(LEN(E5#)=4,"0"&E5#,E5#),1)
520-11-2144520254418-12-06025444452020-11-21
621-11-21445211254405-05-34125444452121-11-21
722-11-21445222254420-09-61225444452222-11-21
823-11-21445233254405-02-89325444452323-11-21
924-11-21445244254423-06-16425444452424-11-21
1025-11-21445255254409-11-43525444452525-11-21
1126-11-21445266254427-03-71625444452626-11-21
1227-11-21445277254412-08-98725444452727-11-21
1328-11-21445288254429-12-25825444452828-11-21
1429-11-21445299254416-05-53925444452929-11-21
1530-11-2144530354413-09-09035444453030-11-21
1601-12-21445311354429-01-37135444453101-12-21
1702-12-21445322354416-06-64235444453202-12-21
1803-12-21445333354402-11-91335444453303-12-21
1904-12-21445344354420-03-19435444453404-12-21
2005-12-21445355354405-08-46535444453505-12-21
2106-12-21445366354421-12-73635444453606-12-21
2207-12-21445377354409-05-01735444453707-12-21
2308-12-21445388354424-09-28835444453808-12-21
2409-12-21445399354410-02-56935444453909-12-21
2510-12-2144540454409-06-12045444454010-12-21
26
27checks initial data
28
Encoding-Decoding
Cell Formulas
RangeFormula
A4,C4,E4,I4,K4A4=FORMULATEXT(A5)
A5:A25,C5:C25A5=SEQUENCE(21,,TODAY())
E5:E25E5=AREVERSE(C5#,1)
G5:G25,M5:M25G5=E5#
I5:I25I5=IF(LEN(E5#)=4,"0"&E5#,E5#)
K5:K25K5=AREVERSE(IF(LEN(E5#)=4,"0"&E5#,E5#),1)
Dynamic array formulas.
 
That CONCAT MID SEQUENCE LEN LEN sounds vaguely familiar... ;)
 
Haha?... I know why!! Because I have posted same formula 1h before you did.
If you go to comment section of the YT video, and choose "SORT BY", "Newest first" you will find my post, 1h before you. ?
I never used LEFT(RIGHT...construction in my functions, always MID, even in my very first iteration of this function, back on april 2021
See YT screen capture attached. ?✌
 

Attachments

  • reverse2.jpg
    reverse2.jpg
    67.6 KB · Views: 11
Haha?... I know why!! Because I have posted same formula 1h before you did.
If you go to comment section of the YT video, and choose "SORT BY", "Newest first" you will find my post, 1h before you. ?
I never used LEFT(RIGHT...construction in my functions, always MID, even in my very first iteration of this function, back on april 2021
See YT screen capture attached. ?✌
Well, what do you know!... Great minds think alike (and the younger ones are quicker to respond ;))
Truthfully, I didn't see your comment before, but the LEFT-RIGHT trick triggered the same response.
As mentioned online: the only downsides are: needing the LEN function twice and a formula that looks slightly more complicated because of the arguments.
The plus sides are: we don't need to construct an "escalating" vector of strings of which we only need the first characters.
So for very large strings (and/or an entire column of those things) our formula is noticeably more efficient. For small problems, the performance difference is inconceivable.
 
I know you did not see it. Sometimes, YT, has this glitch. Hides comments from the main "wall" but reveals them if we click "Newest first". Not the first time when this happened. Sometimes they disappear entirely to other users even if for the one who posted it is there. To check how others are seeing our posts we have to sign out and check if is really there.
Anyhow, was fun when I saw you came with same idea. ✌️?
 

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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