Chandoo takes the Excel Expert Live Challenge | PALINDROMES!

[@DimEarly]

I loved this one! I've come across this 'feature' with emojis before. The issue is that some emojis are a single character, but have a length of 2. LEFT and RIGHT treat them as one character, but MID and LEN treat them as two. When you use MID to split the two halves of an emoji, they don't render right (they just show up as boxes), but you can CONCAT the two pieces back together to get the emoji back. You can even CONCAT the first half of one with the second half of another to get a new emoji. I realize this is exactly the kind of over-engineering that Chandoo was (probably sensibly) avoiding, but just for fun, this is a formula you can use to reverse text that includes double-wide emojis: =LET(input,A1,REDUCE("",SEQUENCE(LEN(input)),LAMBDA(a,v,a&RIGHT(MID(input,1,LEN(input)-LEN(a)))))) I would have used REDUCE to remove the punctuation too (you can use it to SUBSTITUTE out a series of characters), but Chandoo's TEXTSPLIT approach was much nicer!

[@EamonnCottrell]

Incredible. And interestingly, as I tried to reproduce the challenge in a Google Sheet, I hit the same wall when it came to emojis. I tried your =LET solution and it does not work. �🐠🞸🎸🀠🞸🎸🀠🙌� was the output I got in Google Sheets. Weird that it almost works, but not quite. Either way, I'm impressed by the programmatic solution, @DimEarly and thanks for the video @OzduSoleiDATA

[@chandoo_]

It was great doing the challenge with you Oz. I really loved the "curve balls ⚾" that kept coming my way.

[@OzduSoleilDATA]

Thanks so so much for doing this. It meant a lot to me. 🙏🏼

[@carohitmaloo4076]

Chandoo's explanation of the steps and process was truly impressive and captivating. His ability to communicate complex ideas in a clear and concise manner was remarkable. The way in which Chandoo broke down the process into manageable steps was particularly effective, as it made the task seem less daunting and more achievable. Thanks Oz for bringing him in!

[@OzduSoleilDATA]

🙏🏼

[@Wallawallawallawalla]

The more i watch of the video this more impressed i am. You’re a damn good host! How many people would’ve thought to show an animation displaying info about the guest’s native language that they brought up? That’s awesome man

[@OzduSoleilDATA]

🙏🏼

[@captvo]

Absolutely awesome to see your "Excel"lent minds and thought processes at work! The community is blessed to have experts like you'll sharing your skills and experiences. I was taken up by this challenge and wanted to see if it could be done in PowerQuery. Honestly, am just a beginner in M-Code....went to the Microsoft PowerQuery M documentation and found there are two functions "Text.Remove" and "Text.Reverse" available, which are perfect for solving this. Works fine with texts but for texts with emojis, just like Chandoo discovered, the output comes out as boxes. Somebody could shed some light to find a way around this.

[@AlessandroTrovato]

Que dupla!!!! Fantastic!

[@0rtli]

Chandoo is an undeniable master! I extend my appreciation to Oz for granting us this valuable opportunity to witness this remarkable challenge.

[@quadriatharutech]

Chandoo really knows his onions. he was very calm. With his experience , he knows the kind of problem he's going to encounter. you can't catch him off - guard. Shout out @Oz for this challenge. Keep them coming.

[@swilson1958]

Love working through the thought processes! Thanks.

[@sachinbizboy]

Thanks. I wasnt aware both of you know each other so well. I follow Chandoo since 2012.

[@chrism9037]

Fantastic, great video Oz!

[@pavelb.3733]

Thanks for getting to know Chandoo 😊

[@JustArtsCreations]

Love Chandoos content he makes great tutorials

[@spilledgraphics]

9:30 = start easy. yes! Oz, loving the "uuuu's" are back, thank you!

[@spilledgraphics]

Bubblelish-psychedelic-dancing robot on minute 28:21 = say whaaaat ?!??!

[@AccessAnalytic]

Great stuff, those Emojis are a killer. I much prefer Chandoo's approach to the way I tackled the removal of Spaces full stops etc which was to filter out UPPER CASE letters that didn't fall in the UNICODE range of 65 to 90. Interestingly Power Query has Text.Reverse which would be interesting here.

[@OzduSoleilDATA]

I didn't know about Text.Reverse. Thanks for that. I tried it and it works great on text. The emojis, however, show up with more weirdness partly because some emojis translate into PQ and others translate as blank boxes. Interesting ...

[@hari60700]

Chandoo is a legend in Excel.

[@OzduSoleilDATA]

He is, indeed! And it was a real honor to have him do a challenge with me. 🙏🏼

[@hari60700]

Always a Lot more to learn from legends.

[@huseyinburaktasci1638]

Another inspirational content from Chandoo! Thank you Oz for giving that chance.

[@patrickschardt7724]

If I remember correctly older functions can’t handle character codes beyond 255 aka ASCII since UNICODE wasn’t around or widely used when functions like MID was released. It’s neat how some characters are two bytes like most of the symbols and emoji’s

[@syadirars]

I wonder if the unsolved can be solved using VBA 🤔

[@OzduSoleilDATA]

Good question

[@GrainneDuggan_Excel]

I hope you keep these challenges coming, Oz. They are inspiring and intriguing. I want to test why Chandoo did not use a list inside SUBSTITUTE. It will be interesting to test how PQ deals with Emojis.

[@OzduSoleilDATA]

The emojis are tricky. Some will show in Power Query, others won’t. The poop emoji translated but the pretzel becomes a blank box. This, however, was literally the first time I saw emojis act weird in formulas.

[@slayya87]

As Oz said, a big part of this is how you think about the data and how it's relevant. And in the real world, if you have two oddball rows of data, you don't have to build a complex solution around them to get good results.

[@Chris-im3ys]

How would you run a check to see if the original column contains weird symbols?

[@roupenyeremian6801]

This is one of those exercises that just brainstorming for ideas and see what works and not is suited for. Great video and loved Chandoo's formulas. For instance, the MID formula - I've never used it and honestly I have to go back and see what it does. CONCAT formula - just love it - great way of using it in this case. QUESTION: Assuming the data isn't really long, could you have visually scanned the data for emojis, assign a value to each distinct emoji (perhaps create a lookup table), and then substitute each emoji with that value, and then check if it is a palindrome. The final result could stay as it is without the emojis but if you wanted them back, just go back and use the lookup table in "reverse" - that is - replace the value with the emoji? But I agree with Chandoo's comment that maybe it's better in those cases to visually confirm.

[@mikatu]

Yes, most of the challenge could be done by manual manipulation. Mid is just a function to select the middle of a cell, selecting the start point and the lengt. It is like left and right function.

[@barttitulaerexcelbart9400]

Thank you Chandoo & Oz, you are a fabulous duo. As for the last minutes, I was thinking of fuzzy logic, Oz you explained this in your PQ course on LinkedIn.

[@OzduSoleilDATA]

AH! Fuzzy Logic. that would be an interesting angle.

[@patrickschardt7724]

Two great minds. I can’t wait to see your solutions. Both of you have helped me solve many issues with Excel and Power Query

[@OzduSoleilDATA]

🙏🏼

[@FRANKWHITE1996]

Two masterminds in the same video ❤

[@73p3ro]

Oz, put on your cowboy clothes, mount your Creole sorrel, get your lasso ready, put on your Gambler and bring Cristiano Galvão I'm curious to know how he gets out of one of these pitfalls! Don't let him get into the subject of carnival or football!

[@Wallawallawallawalla]

Hey man i love the channel and your personality. Can i support you with a new mic?

[@OzduSoleilDATA]

I do need to sort out my microphone situation.

[@Wallawallawallawalla]

@@OzduSoleilDATAit keeps removing my contact info. Not sure how to move forward

[@OzduSoleilDATA]

@@Wallawallawallawalla connect with me via LinkedIn. I'm the only Oz du Soleil.

[@tiafranks8283]

Would anyone be so kind to point me In the direction of what videos might be useful for drop down boxes. and a formula with the selection from the drop down boxes ( auto documentation formula) if that makes sense. I’m new to xcel

[@excelpro365_1]

Thank You Very much Oz and Chandoo. This is a great tutorial and challenge. After the first clue from sequence and len functions, I thought, Chandoo was going straight to use =+CONCAT(MID(H3,SEQUENCE(LEN(CONCAT(LOWER(TEXTSPLIT(G3,{".",","," ","?","!","-"})))),,LEN(CONCAT(LOWER(TEXTSPLIT(G3,{".",","," ","?","!","-","_"})))),-1),1))=CONCAT(LOWER(TEXTSPLIT(G3,{".",","," ","?","!","-"}))). Let function could come to reduce the formula length though. He said he was not going to be too complex in his bid to solving the challenge and I love that. He made everything so simple for the layman. Thank You Two!

[@OzduSoleilDATA]

YES YES YES! Chandoo does connect with regular folks.

[@MonteBStJohns]

@oz - I can't wait for your next "ARTIST" 🎨 PLEASE - Whatever you gotta do to bring us MORE sessions with this "thinking out loud" format - let us know. I've watched this a number of times and as a "nerd and musician/song-writer" I feel like I'm watching a "series"of ARTIST's 🎨performing their craft. 🎸🎻 It has the feeling I get when I watch another favorite KZbinr (@Rick Beato)! Which Power Platform Artist is NEXT on your list?

[@mikatu]

The problem with chandoo is that he overcomplicates everything.

[@schymi841]

Chandoo did a great job but Oz I have to be honest here. When you're giving someone a test to solve I would expect that you already know the solution and all the potential obstacles that you may run into. Maybe this task cannot be solved using Excel so you put Chandoo in really bad situation here... But if it can be solved then what's the solution?😊

[@OzduSoleilDATA]

I do feel awkward. But the unexpected turn was valuable to show because I’ve been there in many real, paid, projects and have had to decide how far do I chase something and have I done enough to complete the rest manually? There’s always something weird. Still. Your point can’t be dismissed. I shouldn’t have also been surprised when Excel started showing boxes instead of values. A lesson for me.

[@slayya87]

IMHO Emojis shouldn't even show up in a spreadsheet in the first place. If you have Emojis in you spreadsheet, please reevaluate your life choices.