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
Author: Vijay Verma
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…