Regular expressions are my favourite. They’re amazing for processing data of all kinds, especially in the context of data input by many people because it recognises patterns. They’re also so universal, which makes them great for AppSheet, which integrates data from outside sources. My Google Sheets are full of array formulas that use them. Extract and Match are my favourites, but Replace makes the occasional appearance as well.
For example, I’m trying to extract the time digits from a time stamp right now, because they’re added to unique string.
TIMESTAMP: 5/19/2018 00:00:00 (WANT 000000)
Easy with REGEX: =RIGHT(RegexReplace(To_Text(A1),"\D+",""),6)
With AppSheet: =SUBSTITUTE(TIME([TIMESTAMP])-“00:00:00”,":","")
(side note: I’ve seen a lot of threads asking for 24-hour time format selection regardless of locale. This would be great!! I’m calculating a duration here to get the correct numbers.)
I’ve been figuring out how to use substitute a lot, but it gets tricky when dealing with multiple characters and substrings (e.g. phone numbers, which everyone inputs differently). Setting validation rules and using lots of substituting works, but regex is just amazing for standardization!!
Please, add my vote for regular expressions!!