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.
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.
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,""))
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!
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 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
dsd@ 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.
Last updated: October 4, 2019 11:47 AM