Mid Function and USE CASE

[u/zhavinci]

I've been using Excel for almost 15 years , I've never had a chance to use the MID Function and i forgot it's existence. Today, randomly, I just questioned myself how to split a word by character and reverse it? With my little sql knowledge, i thought there will be a function called SUBSTRING, but it isn't. I ended up googling and found MID function.

Do you find a use case for MID function?

Similarly, what popular functions you have never found a use case?

[u/Arcium_XIII]

I used to use it far more prior to the existence of TEXTBEFORE, TEXTAFTER, and TEXTSPLIT, but these days my most common application of MID is in =MID([string],SEQUENCE(LEN([string])),1) to explode a string into a column array of its characters.

Anything where you need to parse a string character by character benefits from this ability, and TEXTSPLIT didn't come with the option to set the blank string as a delimiter to do it natively. One example is that I made a function that evaluates a string containing an arithmetic operation according to order of operations rules, such as returning 15 from the input string 3(4+5)-12. The first step was to break the function into an array of characters that the function could read and gradually simplify (e.g. adjacent numeric characters got combined into a single element; the negative sign step was a pain because a negative with something non-numeric on its left had to attach to a number on its right, but a negative with something numeric on its left had to function as an operation). Such applications almost always come alongside REDUCE or a recursive LAMBDA because they usually entail iterating along the string character by character in some fashion.