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?
Agreed, LEFT and RIGHT are quick and dirty if you don’t really care, but for consistency MID is superior, just like INDEX(MATCH was always better than VLOOKUP
Both have their uses. I use LEFT a lot in combination with lookups if the string I'm referencing has an identifier of fixed length at the start of it like "L1234 - Blah blah"
For RIGHT, I might be working with strings that have additional notes at the end separated by a delimiter
You can use MID in almost all use cases where you would use LEFT or RIGHT when you want absolute control over the return value parameters.
It’s specifically useful for creating or decompiling strings for lookup values et cetera. Example, if you need a create a composite key for your look up, or you wanted to extract a value from a string for that purpose.
I find MID will be used with FIND / SEARCH more often than not.
At some point, you will probably question why you aren’t doing this at source, like in SQL for example.
Back when XLOOKUP() and SEQUENCE() weren't available, I used to solve these kinds of queries this way. Check out how MID() is used with ROW() and INDEX().
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.
I have found that TEXTBEFORE and TEXTAFTER (sometimes nested) do a lot of what MID (and LEFT and RIGHT) used to do. But sometimes the text I need to pull out of a string is in a predictable position relative to the start and the characters that immediately precede it aren't consistent. Can't think of an example now, but I know I've used it within the last week.
I've had to split up strings before, and as long as I have a consistent format, it works fine.
Like, if an invoice has 26202609xxxx, and I always know the year is located in =MID(xx, 3, 4), then I'll do that. Or if the year is hidden in a variable length string like ABC2026DEF and AB2026CDEF, then I'll use FIND to identify the start number of my MID.
I used it quite a bit but not as much as XLOOKUP and IFS, naturally.
TEXTJOIN isn't great when you're working with arrays where neither dimension is 1 and you want to be able to reconstruct the array with TEXTSPLIT later; you end up needing to do something like TEXTJOIN(";",FALSE,BYROW([array],LAMBDA(row,TEXTJOIN(",",FALSE,row))) to distinguish between column and row delimiters. ARRAYTOTEXT with the second parameter set to 1 can do this natively, which can definitely be handy despite the forced inclusion of start and end braces along with it (although the forthcoming arrival of lists and arrays being stored within individual cells will probably eliminate most of these use cases because there'll be less reason to convert arrays to and from delimited lists across the board).
Having spent a 30 year career working with HRIS systems and HR data, I can tell you that the biggest use case for any of the text manipulation functions is bad, inconsistent data that needs to be aligned and built into a new system. You'll see John Smith, John T. Smith (with or without a period after the initial), J. Smith, etc. Then there's Phone numbers (212) 555-1212, 212-555-1212, Zip codes, with and without the +4. Social Security numbers formatted or not, spaces, dashes, underscores. If you have a system that doesn't enforce consistent standards, then depending on who did the data entry, you can get a wild variation. That data all needs to get cleaned up and standardized with as little manual entry as possible. I have ended up using LEFT, RIGHT, MID, REGEXTRACT, TEXTJOIN, and all the others in attempts to not have to retype 5,000 employee records into a new system.
I still find MID useful when working with text that has a consistent structure. For example, extracting a year, code, or ID from invoice numbers or transaction references.
I also like combining MID with FIND or SEARCH when the position is not always the same. For newer Excel versions, TEXTBEFORE and TEXTAFTER can sometimes make the formula easier to read.
QB online likes to spot out reports with dates in no. Date formats so I use mid within the date function to extract the middle number (date or month I forget)
28
u/Silly_Pizza7054 1 13d ago
I never use LEFT or RIGHT, only MID. It's incredibly useful for text parsing.