Excel SUBSTITUTE Function Trick
Jan 09, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
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.
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.
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.
Start 2018 with an upgrade to your Excel skills. Get lifetime access to 3 advanced courses, at 86% off the regular price. Go through the courses, from Excel with Business, at your own pace, and take all the time you need.
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)
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.
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: https://www.contextures.com/newsletter/excelnews2018/20180109ctx.html
ddalgleish @ contextures.com
Last updated: May 4, 2018 3:29 PM