Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: TEXTJOIN function

TEXTJOIN function: Description, Usage, Syntax, Examples and Explanation

What is TEXTJOIN function in Excel? TEXTJOIN function is one of TEXT functions in Microsoft Office Excel that TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. Note: This feature is available…

How to strip non-numeric characters in Excel

To remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. Formula {=TEXTJOIN(“”,TRUE,IFERROR(MID(A1,ROW (INDIRECT(“1:100″)),1)+0,””))} Note: TEXTJOIN will return the numbers as text, for example “100,”500″, etc. If you want a true numeric result, add zero, or wrap the entire formula in the VALUE function. TEXTJOIN was added in Excel 2016 + Office…

How to strip numeric characters from cell in Excel

To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. Formula {=TEXTJOIN(“”,TRUE,IF(ISERR(MID(A1, ROW(INDIRECT(“1:100”)),1)+0), MID(A1,ROW(INDIRECT(“1:100″)),1),””))} Explanation In the example shown, the formula in C5 is: =TEXTJOIN(“”,TRUE,IF(ISERR(MID(B5, ROW(INDIRECT(“1:100”)),1)+0), MID(B5,ROW(INDIRECT(“1:100″)),1),””)) Note: this is an array formula and must be entered with control + shift + enter. How this formula works…

How to translate letters to numbers in Excel

To translate letters in a string to numbers, you can use an array formula based on the TEXTJOIN and VLOOKUP functions, with a defined translation table to provide the necessary lookups. Formula {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(A1,ROW (INDIRECT(“1:”&LEN(A1))),1))),xtable,2,0))} Explanation In the example shown, the formula in C5 is: {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(B5,ROW (INDIRECT(“1:”&LEN(B5))),1))),xtable,2,0))} where “xtable” is the named range E5:F10. Note: this is an array formula and must be entered with…

Join cells with comma in Excel

To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. Formula =SUBSTITUTE(TRIM(A1&” “&B1&” “&C1&” “&D1&” “&E1),” “,”, “) Explanation In the example shown, the formula in G5 is: =SUBSTITUTE(TRIM(B5&” “&C5&” “&D5&” “&E5&” “&F5),” “,”, “) Working from the…

List holidays between two dates in Excel

To list holidays that occur between two dates, you can use a formula based on the TEXTJOIN and IF functions.  Formula {=TEXTJOIN(“, “,TRUE,IF(dates>=start,IF(dates<=end,holidays,””),””))} Note: the TEXTJOIN function is new in Excel 2016. Explanation In the example shown, the formula in F8 is: {=TEXTJOIN(“, “,TRUE,IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,””),””))} This is an array formula and must be entered with control + shift + enter. How this formula works At a…

How to abbreviate names or words in Excel

To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms. Only capital letters will survive this formula, so the source text must include capitalized words. You can use the PROPER function to capitalize words…

Highlight duplicate rows in Excel

This tutorial shows how to Highlight duplicate rows in Excel using the example below; Formula =COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1) Explanation Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you’ll need to use your own formula, as explained below. If you want to…

Multiple matches in comma separated list in Excel

This tutorial shows how to work Multiple matches in comma separated list in Excel using the example below; Formula {=TEXTJOIN(“, “,TRUE,IF(range1=E5,range2,””))} Explanation To lookup and retrieve multiple matches in a comma separated list (in a single cell) you can use the IF function with the TEXTJOIN function. In the example shown, the formula in F5 is: {=TEXTJOIN(“, “,TRUE,IF(group=E5,name,””))} This is an array formula and…

Join Text Strings Using Concatenate or ‘& Operator’ in Excel

This example illustrates how to concatenate (join) strings in Excel. When joining text and a number, use the TEXT function to format that number. 1. Simply use the & operator to join strings. Note: to insert a space, use ” ” 2. The CONCATENATE function produces the exact same result. 3a. The CONCAT function in Excel 2016 produces the exact same result. 3b. The CONCAT…