3 ms·
This is completely orthogonal to the full scope of LAMBDA, But it did make me laugh to see that the first example involved a classic and painful usecase (extrac
by danso 6y ago
This is completely orthogonal to the full scope of LAMBDA, But it did make me laugh to see that the first example involved a classic and painful usecase (extracting 2 letters amid a string of numbers), which in Excel has to be written as:
=LEFT(RIGHT(B18,LEN(B18)-FIND("-",B18)),FIND("-",RIGHT(B18,LEN(B18)-FIND("-",B18)))-1)
But if only Excel would support regular expressions like Google Sheets [0], could be done as easily as:
=REGEXEXTRACT(A2, "[A-Z]{2}")
I'm sure adding regex isn't a trivial thing, but simple pattern extraction seems such an absolutely massive usecase for every everyday user that just I cannot fathom why Microsoft won't support regex. It would make Excel vastly more powerful for its purportedly non-coding users, especially since GSheets has had it for years now. Maybe someone on the product team believes regex feels too much like "code"? As a triple nested function involving subtr, strlen, and array indexing isn't?
[0] https://support.google.com/docs/answer/3098244?hl=en https://support.google.com/docs/answer/3098244?hl=en
- deleted 6y ago[deleted]
- maxerickson 6y agoThe regex is perhaps still clearer (and in general more powerful), but that Excel method is a catastrophe, not the way it has to be done. =MID(B1, FIND("-",B1)+1, 2)
- craftinator 6y ago> I'm sure adding regex isn't a trivial thing Actually, adding that into the list of Excel functions would be trivially easy. The hard part would be convincing all of the managers that it won't dramatically increase the amount of work they need to do in terms of tech support.
- darwingr 6y agoI found myself going back to this the other day. I learned these long left/right formulas before I learned to program. Rewriting them really made me miss regex.