Say, you want 10 random numbers between 1 to 100. Then you can use following formula =INDEX(SORTBY(SEQUENCE(100), RANDARRAY(100)), SEQUENCE(10)) If rather than 1 to 100, then you can tweak SEQUENCE(100) accordingly. Say you need between 101 to 500. Then replace SEQUENCE(100) with SEQUENCE(500-101+1, , 101) => Note here, 101 is start number and 500 is…
Category: Tips and Tricks
Tips & Tricks 204 – Return Columns in Different Order in XLOOKUP
Use following formula for this =XLOOKUP(M2,C:C, CHOOSE({2,1}, D:D, F:F)) =XLOOKUP(M2,C:C, CHOOSECOLS(D:F,3, 1)) Sample File – T&T_204
Tips & Tricks 203 – Return non-adjacent columns from XLOOKUP
You can use following formula to return non-adjacent columns through XLOOKUP =XLOOKUP(M2,C:C, FILTER(D:F, {1,0,1})) =XLOOKUP(M2,C:C, CHOOSECOLS(D:F, 1, 3)) Sample File – T&T_203
Tips & Tricks 202 – Remove Alphabets from a String
To remove alphabets from a string, for a single cell =CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(26,,97)),,,1)) Using REGEX =REGEXREPLACE(A2, "[a-zA-Z]", "") To remove alphabets from entire range =BYROW(A2:A10, LAMBDA(a, CONCAT(TEXTSPLIT(a,CHAR(SEQUENCE(26,,97)),,,1)))) using REGEX =REGEXREPLACE(A2:A10, "[a-zA-Z]", "") The Excel file for this tips can be downloaded from Tips & Tricks 202 – Remove Text from a String
Tips & Tricks 201 – Remove Numbers from Strings
To remove numbers from a string, for a single cell =CONCAT(TEXTSPLIT(A2, SEQUENCE(10,,0))) Using REGEX =REGEXREPLACE(A2, "[0-9]", "") To remove numbers from entire range =BYROW(A2:A10, LAMBDA(a, CONCAT(TEXTSPLIT(a, SEQUENCE(10,,0))))) using REGEX =REGEXREPLACE(A2:A10, "[0-9]", "") The Excel file for this tip can be downloaded from Tips & Tricks 201 – Remove Numbers from a String
Tips & Trick 200 – Reverse A String
To reverse a string in a cell, following formula can be used =CONCAT(LEFT(RIGHT(A2,SEQUENCE(LEN(A2))))) For many cells in a range, following formula can be used =BYROW(A2:A11,LAMBDA(a,CONCAT(LEFT(RIGHT(a,SEQUENCE(LEN(a))))))) The Excel file related to this tips can be downloaded from Tips & Tricks 200 – Reverse Text
Tips & Tricks 199 – Generate Fibonacci Numbers
You can generate Fibonacci numbers through following formula =REDUCE({0;1}, SEQUENCE(20-2), LAMBDA(a,b, VSTACK(a, SUM(TAKE(a, -2))))) Here, 20 in SEQUENCE(20-2) means 20 numbers need to be generated. So, if you need to generate 100 Fibonacci numbers, just replace 20 with 100 in SEQUENCE. =REDUCE({0;1}, SEQUENCE(100-2), LAMBDA(a,b, VSTACK(a, SUM(TAKE(a, -2))))) The Excel file of this tips can be…
Tips & Tricks 198 – Reverse Multiple Ranges
In last tip, I covered how to reverse a single range. Here, I am going to cover how to reverse multiple ranges. The formula which can be used here to reverse multiple columns (here range is A2:C12) =LET(r, A2:C12, IFERROR(DROP(REDUCE("", SEQUENCE(COLUMNS(r)), LAMBDA(a,b, HSTACK(a, LET(nr, INDEX(r,,b), sr, SORTBY(nr,ROW(nr),-1), FILTER(sr, sr<>0))))), , 1), "")) The formula can…
Tips & Tricks 197 – Reverse a range
To reverse a range in Excel, it is fairly an easy job with SORTBY if you have dynamic array functions =SORTBY(A2:A10, ROW(A2:A10), -1) If you are still on drag and drop versions, here is the formula for drag and drop (Put this formula in a cell and drag it down) =INDEX($A$2:$A$10, MAX(ROW($A$2:$A$10)-ROW($A2)+1)) The Excel file…
Tips & Tricks 196 – Cartesian Product
Suppose there are two ranges A2:A4 = A, B, C B2:B5 = 1, 2, 3, 4 Cartesian product should give the result A1, A2, A3, A4, B1, B2, B3, B4, C1, C2, C3, C4 In Excel the formula would be =DROP(REDUCE("",A2:A4, LAMBDA(a, b, VSTACK(a, b & B2:B5))),1) In Power Query, the formula would be =…