Contextures News 20191022

Excel Find With Wildcards

October 22, 2019

Outlining, find with wildcards, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Outline Groups

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.

Sam file for outlining

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,

  1. copy the modOutlining module to your workbook
  2. copy the code from ThisWorkbook, and paste it onto the ThisWorkbook module in your workbook

Then, to use the shortcuts:

  • Press Alt+Left arrow, to hide outline levels, and press Alt+Right arrow, to show outline levels

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.

AlexJ file for outlining

Find and Replace

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:

  • Select all the cells in column A, then press Ctrl+H, to open Find and Replace
  • In the Find What box, type:   :*
    • The * wildcard represents any number of characters.
  • Leave the Replace With box empty
  • Click the Replace All button, then click OK in the confirmation message

Warning: Make a backup copy of your file before using the Replace All command, just in case things go wrong.

find with wildcard

There are more examples, and a video that shows the steps, on my Contextures blog: Remove Text in Excel Without Macros

Excel Articles

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?

Also see: My Excel Products || Previous Issues

Fall Colour

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.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info


Last updated: October 21, 2019 7:06 PM