The TEXTBEFORE functions was recently released in this video I show 3 uses for TEXTBEFORE, it’s strengths and limitations. In this example we have names and professional designations. The problem is some people have more than one designations, some have 1 designation and some folks have no designations. BUT! We have to be careful. We […]
14 new functions were released into Excel recently. In this video I cover two of them: TOCOL: converts a rectangular matrix into a single column DROP: retrieves a rectangular array and drops (eliminates) columns that aren’t needed. One thing to know: you can drop columns starting from the beginning or end of a range. You […]
I was on a couple of Meetup Zoom calls and Victor Momoh was the host. He showed some fascinating stuff by using XLOOKUP to retrieve a range of data. He then used it to make sums and all kinds of other cool things. I share some of that in this video with you. – Victor […]
My very first need for Excel’s VBA was back in 2009. I had a lot of data and the content I needed was the bold font in the cells. Some how, the non-bold font needed to go away. Back then, some kind soul on an online forum wrote me some VBA code that I didn’t […]
Mr. Excel, Bill Jelen, contacted me with a question that he’d been asked: Can you arrange seating such that every person in attendance will sit at a table with every other person, at least once? The parameters: – 100 people – 10 tables – 10 seats t each table – 11 rounds Bill concluded that […]
Recently, I’ve been getting mail that contains data that I need to get off of the paper and into Excel. Typing the data into Excel is one option. But I figured there must be better ways. In this video I show 2 ways to get data by taking a picture, then converting the text from […]
It’s easy to use Power Query to split by, say, 5 columns. But, what if your data changes and needs to be split by 3 columns or 10 columns? That’s trickier–especially since Power Query will hard-code that 5 in the query and will ignore future changes. This video shows how to get Power Query to […]
Iranian Dr. Excel posted a challenge on LinkedIn: How can you split columns when there are multiple delimiters? Ex: Joe+Lisa+Francine/Kathy Rita&Sal&Gene Samantha/Denise This was interesting for several reasons: 1. It’s very easy to do if you want a 1-and-done solution with just 3 delimiters. However, 2. If you want something truly dynamic, it involves solving […]
In the previous 2 videos we’ve looked at calculating NETWORKDAYS and days off that aren’t just Saturday and Sunday. Thanks for Bill Szysz, Wayne Edmondson, XLarium and others for leading a lively discussion and offering really cool formula-based solutions. (The conversation is on this video: https://youtu.be/njC1AvEU3fg) In this video I show you something I learned […]