Excel Find With Wildcards
October 22, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
In a big worksheet, Excel's Outlining feature can make it easier to work with the data. Set up grouping, then use the sidebar buttons to show or hide the outline levels.
For example, show just the Region totals, or show Quarter totals per Region, or see all the rows.
If you don't like to use the mouse, there's a sample file on my site that lets you show or hide Outline levels with the arrow keys, instead of the sidebar buttons.
The code was written by Sam, and you can download his sample file (UF0009) to see how it works.
After you open the workbook, and enable macros, a bit of code sets up the Arrow keys. To add this code to your workbook,
Then, to use the shortcuts:
For a fancier Outlining system, take a look at AlexJ's button-based method. Click buttons at the top of the sheet, to show or hide the outline sections. Download AlexJ's sample file (UF0008) to try it.
Excel Find and Replace feature is a quick way to clean up some data. Most of the time, you probably use it to look for a specific word, and replace it with a different word.
Did you know that you can use wildcard characters in the Find box too? For example, find any cells that contain a colon, and delete that colon, and any text after it.
To do that in column A:
Warning: Make a backup copy of your file before using the Replace All command, just in case things go wrong.
There are more examples, and a video that shows the steps, on my Contextures blog: Remove Text in Excel Without Macros
Here are a few Excel-related articles that you might find useful or interesting.
Data Viz: Download the papers and case studies presented at last weekend's Visualization for Communication (VisComm) workshop. (Level - Int)
Excel Team: Last week, on Spreadsheet Day, Microsoft's Excel team hosted an "Ask Microsoft Anything" event. You could post your questions on their website, and the team tried to answer them. If you missed the live event, you can still see the questions and answers online. . (Level - All)
Excel Tweets: See what people tweeted about Excel this week. Does your boss know how to change worksheets to portrait mode?
We had another lovely fall weekend, and went for a walk after supper on Saturday. It certainly gets dark early these days, but Daylight Saving Time will end in a couple of weeks.
The trees were still showing their fall colours though, even after the sun went down. This sycamore tree is close to a street lamp, and its orange leaves looked lovely against the dark sky.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191022ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: October 21, 2019 7:06 PM