Contextures

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

SUBSTITUTE Trick

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.

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

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:

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))<=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!)

=A2=SUBSTITUTE(A2,",","",2)

There are more SUBSTITUTE examples on my Contextures Blog.

SUBSTITUTE function

Advanced Excel Training

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.

  1. Advanced Excel - instructor Simon Hurst
  2. Introduction to Data Science - instructor Harold Graycar
  3. Business Analysis - instructor Dr. Chris Littlewood

Click here to see the bundle details. The offer expires on Jan 15th, and use coupon code DEBRAD for an extra $10 off the price. There's more info on my blog, and my review of the first course.

NOTE: These courses are for intermediate and advanced Excel users, so if you're a beginner, start with the Basic Excel or Intermediate Excel course instead.

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: https://www.contextures.com/newsletter/excelnews2018/20180109ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

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: May 4, 2018 3:29 PM