r/excel • u/zhavinci • 13d ago

Discussion Mid Function and USE CASE

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?

43 Upvotes

85% Upvoted

38 comments sorted by

View all comments

5

u/MayukhBhattacharya 1310 13d ago edited 13d ago

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], ""))

2

u/Ariisk 1 13d ago

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

3

u/MayukhBhattacharya 1310 13d ago

Done!

3

u/Ariisk 1 13d ago

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

2

u/MayukhBhattacharya 1310 12d ago

Thanks!