Hey there,

Ever tried doing a partial search in Excel, where you're looking for a specific set of characters within a larger text string or cell?

It can be surprisingly tricky - and most people get it wrong.

Let’s say you’re working on a video project and want some help from your colleagues.

So you go to the Employee Details spreadsheet, to create an automated list of everyone with the word ‘video’ in their Skills section, for example 'video editing'.

To create this list, most people start with the FILTER function.

For ‘array’ they’ll select the whole table [=FILTER(A2:B12)], for ‘include’ they’ll select the column containing the text they want to search, then make that equal to “video”: =FILTER(A2:B12,B2:B12=”video”).

Sounds great. But this won’t work, because you’re only getting exact matches to “video”!

And no one puts just ‘video’ in their Skills section. 😅

And you can’t use asterisks (“*video*“) to represent any number of characters before or after “video”, because asterisks aren't compatible with the FILTER function.

Here’s how to build a partial search formula that works:

1. Start with the FILTER function as normal, selecting your whole table as the array.
2. For the “include” argument, start with ISNUMBER.
3. Then use SEARCH, and write down “video” as the text you want to find.
4. Finally, select the range of cells you’re searching in.

Your formula should look like this: =FILTER(A2:B12,ISNUMBER(SEARCH(“video”,B2:B12))).

Press Enter, and you’ll get a complete list of the cells you’re looking for!

🚀 Become an Excel Expert - Get 20% Off Global Excel Summit Recordings

I learned so much at the Global Excel Summit. But just because it’s finished, doesn’t mean you’ve missed out!

They’ve has just announced two event recording packages that cover every minute of the sessions, and are packed with information to take your Excel and Power BI skills to the next level.

Choose the Conference package for lifetime access to:

• My Keynote talk
• 30 Main Stage sessions
• 4+ hours of MOS & MOE Exam Prep
• Speaker presentation & working files.

Or take things a step further with the Masterclass package, which includes six masterclasses on advanced (and very powerful) Excel and Power BI features. You can also buy Masterclasses individually.

Best of all, my audience gets a 20% discount on all packages! Just follow this link and use the code LEILA at checkout to get your discounted package.

​Start learning now 🚀

🎬 My Favorite OneNote Shortcuts

OneNote has loads of features, but it can take a long time to find anything.

In this video I cover all of my top OneNote keyboard shortcuts to streamline your workflow and make you more productive.

My favorite shortcut is Alt + Shift + Up/Down, which lets you move rows easily in OneNote without cutting/pasting. It also works great in tables.

🤓 Geeky News

✅ Microsoft Planner - New Recurring Tasks Feature
This was a highly-requested feature. You can now set up automatic repeating tasks in Planner, with many recurring interval options including daily, weekly, and monthly. This is great for tracking recurring tasks, like filling out your weekly 1:1.

💁‍♀️ Microsoft Forms - Images as Answers + Instant Polls
Microsoft Forms has traditionally had less features than its older cousin, Google Forms. But in their January update Microsoft added a lot of functionality. For example, you can now submit images as answers, conduct instant polls, and send your forms to multiple channels (eg Outlook, Teams, QR code, Facebook, and Twitter).

💚 My Favorite Things

🎙️ Podcast - Business Made Simple
BMS is a great business podcast, complete with interviews, analysis, and real-life case studies. I love how they summarize and give concrete action steps after each episode. This is a great technique to bear in mind if you’re an educator, or give in-house training.

🤖 Bing AI Chatbot - My First Question
I got access to Bing’s AI chatbot for the first time this week. My first question was “what are some advanced topics people struggle with in Excel”, and it was a nice surprise to see my name pop up!

🚀 Power Stories

Congratulations to Haley on getting her certificate from our Financial Analysis course! We love to see our students succeeding 😊

Got a success story about using your XelPlus skills? I’d love to hear it! Hit reply to share the details, and inspire other students. 😇

See you next week,

Leila

Want more?

▶️ Subscribe on YouTube​

🖇️ Follow me on LinkedIn​

🥇 Join 300,000+ people in our courses​

📣 Want to advertise on XelPlus Weekly? Get in touch here.