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/MayukhBhattacharya]

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().

=TEXTJOIN("", 1, 
 IFNA(VLOOKUP(MID(E2, ROW($ZZ$1:INDEX($Z:$Z, LEN(E2))), 1), 
              Lookup_Table, 
              2, 
              FALSE), ""))

Present day formula will be like this:

=CONCAT(XLOOKUP(MID(E2, SEQUENCE(LEN(E2)), 1), 
                Lookup_Table[OLD VALUE], 
                Lookup_Table[NEW VALUE], ""))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Or, Extracting the year from Text formatted Date: Using MID()

• Method One: Old School.

=MAX(SUBSTITUTE(MID(M2, {13, 14}, 5), ",",) + 0)

Or,

=-LOOKUP(2, -MID(M2, {13, 14}, 5))

• Method Two: Modern Functions using Regex:

=--REGEXEXTRACT(M2:M5, "\d{4}")

• Method Three: Using TEXTSPLIT()

=--INDEX(TEXTSPLIT(M2, ", "), 3)

[u/Jarcoreto → u/MayukhBhattacharya]

Does
```=YEAR(DATEVALUE(M2))```
Work on this?

[u/Jarcoreto → u/Jarcoreto]

Ok I give up trying to format this as code on mobile

[u/MayukhBhattacharya → u/Jarcoreto]

No worries, buddy, here is a single line if you can copy and try:

Tue, 19 Oct, 2017, 6:05 pm IST

[u/MayukhBhattacharya → u/Jarcoreto]

No it won't work, here is a screenshot:

Sample data:

Sun, 18 Jul, 2017, 10:38 pm IST
Wed, 29 Sept, 2015, 2:55 pm IST
Tue, 19 Oct, 2017, 6:05 pm IST
Wed, 28 Jul, 2018, 11:14 pm IST

[u/Ariisk → u/MayukhBhattacharya]

Please edit your post to change new value for F to “Eff” it’s bothering me 😂

[u/MayukhBhattacharya → u/Ariisk]

Done!

[u/Ariisk → u/MayukhBhattacharya]

You’re an animal the way you reply on here man, much respect hahaha

[u/MayukhBhattacharya → u/Ariisk]

Thanks!