Contextures News 20180109

Excel SUBSTITUTE Function Trick

Jan 09, 2018

Check cell contents with SUBSTITUTE, 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 supports the free info on my site!

Table Filter Spinner

Quickly filter an Excel table with a spin button - click up and down buttons to filter by next or previous item in the list. This is handy if you want to check each product, and don't have room for a Slicer on the worksheet.

A macro runs when you click the spin button. That changes the filter in a pivot table, and the Excel table filter changes to match the pivot table.

There are setup details on my website, and a sample file to download.

scroll through filter items


Do you use the SUBSTITUTE function very often? What do you use it for?

I often use it to count items in a cell, when they're separated by commas or spaces.

  1. Get the number of characters in cell A2
  2. Get the number of characters in cell A2, with all the commas are removed
  3. Subtract amount 2 from amount 1, and add 1

This formula calculates the number of commas in cell A2, and then adds 1, to get the number of items.


If only 1 or 2 items should be entered in the cell, use a slightly different formula, to check the number of commas -- there should be 0 or 1:


Or, use this shorter formula. It uses 2 as the 3rd argument, to specify which instance number to replace. (If you omit the 3rd argument, all instances are replaced.) There should NOT be a 2nd comma, so replacing it won't change the value in cell A2, if there are only two items. (Thanks UniMord!)


There are more SUBSTITUTE examples on my Contextures Blog.


Advanced Excel Training

This offer is no longer available.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Data Viz - Here's a round up of the best data visualization lists from last year, compiled by data journalist, Maarten Lambrechts. This should inspire you for your 2018 projects. (Level - All)

Spreadsheets - Manny Medrano, a Harvard undergrad, deciphered the knotted strings of an Inca khipu, which was used to record censuses and taxes -it's "like an ancient Excel spreadsheet". And for a modern use of spreadsheets, watch this entertaining video on Excel and HDR photography. (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Penguin Weather

Happy New Year! We had a lovely Christmas celebration with our family, and stayed inside most of the time, avoiding the snow and extreme cold. On Christmas Eve, it was snowing lightly, and not too cold, so we went for a walk to see the Christmas lights. That's my son, daughter, and granddaughter, walking like penguins -- we've watched that Monty Python "Silly Walks" sketch too many times.

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version:

Debra Dalgleish
ddalgleish @

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.



Get weekly Excel tips from Debra


pivot power free






Last updated: February 24, 2021 9:54 AM