Category Archives: TEXTJOIN

# Excel Super Links #73 – shared by David Hager

 

Changing Excel Icon & Creating Workbook Shortcut With A Custom Icon

http://www.myengineeringworld.net/2014/10/change-excel-icon-workbook-shortcut-vba.html

Complete list of all the xlConstants found in Microsoft Excel

http://www.excelgaard.dk/Lib/xlConstants/

Numbering Sequence of Events in DAX

https://www.sqlbi.com/articles/numbering-sequence-of-events-in-dax/

Year to Date in Previous/Prior Year in DAX

https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/?nabm=0

TEXTJOIN: Multiple Lookup Values in a Single Cell (With/Without Duplicates) Using Only #Excel Formulas by David Hager

https://dhexcel1.wordpress.com/2017/04/04/multiple-lookup-values-in-a-single-cell-withwithout-duplicates-using-only-excel-formulas-by-david-hager/

# Excel Super Links #65 – shared by David Hager

 

Data Cleansing with R in Power BI

https://powerbi.microsoft.com/en-us/blog/data-cleansing-with-r-in-power-bi/

5 + 1 Reasons You Should Use Excel’s Quick Analysis Option

http://myexcelonline.com/blog/5-1-reasons-you-should-use-excels-quick-analysis-option/

Excel, LDAP and Active Directory

https://sites.google.com/site/beyondexcel/project-updates/excelldapandactivedirectory

Word Clouds with Microsoft Excel

http://www.clearlyandsimply.com/clearly_and_simply/2015/02/word-clouds-with-microsoft-excel.html#more

Creating an Excel Table of Components By Product From a List Using the TEXTJOIN Function By David Hager

https://dhexcel1.wordpress.com/2017/01/07/creating-an-excel-table-of-components-by-product-from-a-list-using-the-textjoin-function-by-david-hager/

 

ESL#31-60 A Valuable Collection of Excel and Power BI Links to Great Excel and Modern Excel Techniques Vol 2 by David Hager

 

Here is volume 2 of my Excel Super Links. I really did not think that I would still be posting these links to issue #60, but a lot of people seem to like these posts, so I will continue. Enjoy this collection (150 links).

You can download the collection here.

Excel Super Links 31-60

#Excel Super Links #55 – shared by David Hager

Simple 3D to 1D Array Transformation in Excel

https://excelxor.com/2016/04/08/advanced-formula-challenge-13-single-array-containing-all-entries-from-a-given-range-in-multiple-worksheets/#comment-2684

Excel Conditional Formatting for Statistical Significance

http://georgejmount.com/excel-conditional-formatting-for-statistical-significance/

Progress Doughnut Chart with Conditional Formatting in Excel

https://www.excelcampus.com/charts/progress-doughnut-chart/

Customizable Tooltips on Excel Charts

http://www.clearlyandsimply.com/clearly_and_simply/2016/08/customizable-tooltips-on-excel-charts.html#more

#Excel Short & Sweet Tip #16 (Multiple Delimiters with TEXTJOIN for Custom Formatting) by David Hager

https://dhexcel1.wordpress.com/2017/05/24/excel-short-sweet-tip-16-multiple-delimiters-with-textjoin-for-custom-formatting-by-david-hager/

#Excel Super Links #53 – shared by David Hager

Decoding M – Exploring the YouTube Data API with Power BI

http://theexcelclub.com/decoding-m-exploring-the-youtube-data-api-with-power-bi/

Excel Dates Displayed in Different Languages

http://www.myonlinetraininghub.com/excel-dates-displayed-in-different-languages

Use VLOOKUP to Calculate Discount Percentages

http://www.get-digital-help.com/2017/05/31/use-vlookup-to-calculate-discount-percentages/

Performance Tip: Partition your Tables at Crossjoins where possible – PowerQuery PowerBI

http://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/

Revisiting Using the Excel TEXTJOIN Function To Return Unique Items From A 3D Range by David Hager

https://dhexcel1.wordpress.com/2017/06/01/revisiting-using-the-excel-textjoin-function-to-return-unique-items-from-a-3d-range-by-david-hager/

 

Revisiting Using the Excel TEXTJOIN Function To Return Unique Items From A 3D Range by David Hager

In the following article:

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

I demonstrated a method of obtaining unique items from a 3D range. At the time that this article was written, I was unaware that the TEXTJOIN function accepted native 3D references. See:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

The techniques needed to convert a delimited string to a unique delimited string have already been discussed.

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

So, with the starting point of a delimited string obtained by using 3D reference with TEXTJOIN, the link above will then allow you to create the unique array or delimited string.

To be clear, first TEXTJOIN is used with a 3D range argument to make a delimited string. Then, that string is used in the formula construction described in the link above to make an array of unique items, which can be subsequently used to make a unique delimited string.

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

#Excel Short & Sweet Tip #16 (Multiple Delimiters with TEXTJOIN for Custom Formatting) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

it shows an example at the bottom of the article where the 1st argument of the TEXTJOIN function uses multiple delimiters. I knew that multiple delimiters could be used, but I could not think of an example where that might be useful. Then, I decided that it did have a useful purpose if it resulted in a desirable display result. So the first example is:

=TEXTJOIN(A7:D7, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula =CHAR(10).

As you can see in the figure, each record appears to be on a single line in the cell (F9).

TJMD1

This same technique can be enhanced further in this way.

=TEXTJOIN(A8:D8, TRUE, A2:D6) where A7:C7 contains commas and D7 contains the formula

=CHAR(10)&REPT(“-“,CELL(“width”,H9)+NOW()*0)&CHAR(10).

With the TEXTJOIN formula example in cell H9, the appearance of the created string makes it very easy to see (in the following figure) each record.

TJMD2

The example file can be downloaded here.

TJ_MultDelimiters