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
Category: Tips and Tricks
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 =…
Tips & Tricks 195 – Reverse Lookup in a Range
Sometimes, you have a dataset where you will need to perform lookup starting from last cell to first cell rather than usual first cell to last cell. With XLOOKUP function, the task has become easier as it gives a parameter to control this. While default is from first cell to last cell but if you…
Power Query – Bypass 3000 cells limit in Enter Data – Tips & Tricks 19
Question – I am trying to "Enter Data" in Power BI that is a large table. I am doing this to avoid a connection and avoid refreshing data. I can copy the data from a csv file but when I paste into Enter Data, I get an error with 3000 cell limit. Answer – 1. Import…
Power Query – Multi Column Exact Match XLOOKUP/VLOOKUP – Tips & Tricks 18
Learn to perform Multi column exact match in Power Query like XLOOKUP, VLOOKUP in Excel. The pbix file used in this can be downloaded from – https://1drv.ms/u/s!Akd5y6ruJhvhvxUlfh5W15hhMqT-?e=vg6eTL