•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Text search formulas with built in functions instead of lambdas
I made two lambda functions to perform the following types of string / character searches, and I'm wondering whether the same functionality is possible with formulas using only built in functions (maybe REGEX?). I'm trying to calibrate whether I'm going overboard with a new toy (lambdas) or if their use is justified.
- ANYCHARS(SRCH, TXT)
- If any character in SRCH is present in TXT, returns TRUE. Otherwise, returns FALSE. Case insensitive.
- ALLCHARS(SRCH, TXT)
- If all characters in SRCH are present in TXT, returns TRUE. Otherwise, returns FALSE. Case insensitive, and character order does not matter.
Code below:
ANYCHARS =LAMBDA(SRCH,TXT, IF(LEN(SRCH)<1, FALSE, OR(ISNUMBER(SEARCH(RIGHT(SRCH,1),TXT)), ANYCHARS(LEFT(SRCH,LEN(SRCH)-1),TXT)) )) ALLCHARS =LAMBDA(SRCH,TXT, IF(LEN(SRCH)<1, TRUE, AND(ISNUMBER(SEARCH(RIGHT(SRCH,1),TXT)), ALLCHARS(LEFT(SRCH, LEN(SRCH)-1), TXT)) )) Here's a screenshot of the functions in action:
[link] [comments]
Want to read more?
Check out the full article on the original site
Tagged with
#financial modeling with spreadsheets
#no-code spreadsheet solutions
#rows.com
#Excel compatibility
#Excel alternatives for data analysis
#Excel alternatives
#lambda
#ANYCHARS
#ALLCHARS
#string search
#character search
#built-in functions
#formulas
#case insensitive
#REGEX
#ISNUMBER
#SEARCH
#TRUE
#FALSE
#OR