Contextures News 20191001

Excel TEXTJOIN Function Examples

October 1, 2019

TEXTJOIN examples, conditional formatting tip, 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.

See Long Rules

If you set up conditional formatting rules, some of the formulas can get pretty long. You can see all your rules in the Conditional Formatting Rules Manager, but only a small part of each formula shows.

Here are the rules for the Highlight Lottery Numbers example on my website, and it has 2 rules - winning numbers are coloured green, and winning tickets are coloured yellow

If you want a quick peek at the formulas, you don't need to click the Edit button - just point to one of the rules, and its full formula will appear in a popup.

Go to my Contextures site for more conditional formatting examples, videos and sample files.

TEXTJOIN Function

In Excel for Office 365 and Excel 2019, you can use the new TEXTJOIN function to combine text from multiple ranges, quickly and easily. If it's not in your version of Excel, try it in the embedded workbook, on my Contextures site. Or, use the ConCat user defined function in any version.

TEXTJOIN has 3 required arguments - delimiter, ignore_empty, and text1. Here's a simple formula, with ", " as the delimiter, TRUE (ignore empty cells), and a list in A2:A8. It returns all the weekday names.

=TEXTJOIN(", ",TRUE,A2:A8)

You can combine TEXTJOIN with other functions, for a more powerful formula. Here's a formula that returns a list of the "Open" days. The formula is array-entered, with Ctrl+Shift+Enter

=TEXTJOIN(", ",TRUE,IF(B2:B8="x",A2:A8,""))

• The IF function checks for an "x" in column B, which indicates a day that the store is open.
• If an "x" isn't found, IF returns an empty string ("") for that day, and TEXTJOIN ignores it, because TRUE is the 2nd argument.

Go to my Contextures site to get the TEXTJOIN sample file, and to see another TEXTJOIN example. Try the embedded TEXTJOIN workbook too, while you're there!

Excel Articles

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

Charts: Excel's chart labels can be tricky to format, and Jon Peltier shares a few tips to prevent the labels from overlapping. Just for fun, can you think of a different formula to add alternating line breaks? (Level - Int)

Excel Tips: Do you use all 7 of the Excel shortcuts that Jon Acampora shared? The first tip is handy if you're revising a formula, and the list of options doesn't appear, e.g. function numbers for AGGREGATE or SUBTOTAL. (Level - All)

Also see: My Excel Products || Previous Issues || Excel Twitter

Fall Flowers

Fall has definitely arrived, here in Canada. There was a big snowstorm in the western provinces, with up to 100 cm of snow predicted for some areas! Fortunately, we didn't get snow in southern Ontario, but the leaves are changing colour, and there was a cool breeze at the outdoor wedding we attended on Saturday.

However, nobody told the rose bush in our back yard that summer is over, and it's enjoying one last hurrah.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191001ctx.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.

Debra Dalgleish
[email protected] contextures.com

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.