Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Load Analysis ToolPak in Excel
  • Reverse List in Excel
  • Remove Duplicates Example in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • Count rows that contain specific values in Excel
  • Approximate match with multiple criteria in Excel
  • How to get last column number in range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation allow weekday only
  • Excel Data validation date in next 30 days
  • Excel Data validation with conditional list
  • Excel Data validation must not contain

Tag: TEXTJOIN function

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

by

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 …

Continue Reading

Join cells with comma in Excel

by

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),” …

Continue Reading

How to translate letters to numbers in Excel

by

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 …

Continue Reading

How to strip numeric characters from cell in Excel

by

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 + …

Continue Reading

How to strip non-numeric characters in Excel

by

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 …

Continue Reading

List holidays between two dates in Excel

by

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 …

Continue Reading

How to abbreviate names or words in Excel

by

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 …

Continue Reading

Highlight duplicate rows in Excel

by

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 …

Continue Reading

Multiple matches in comma separated list in Excel

by

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(“, …

Continue Reading

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

by

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 …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • IF with boolean logic in Excel

Date Time

  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate next day of week in Excel
  • Get date from day number in Excel
  • Convert Unix time stamp to Excel date
  • Display Date is same month in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Categorize text with keywords in Excel
  • Map inputs to arbitrary values in Excel
  • If cell contains one of many things in Excel
  • How to randomly assign data to groups in Excel

General

  • Excel Operators
  • How to calculate percent of students absent in Excel
  • How to generate random number between two numbers in Excel
  • How to fill cell ranges with random text values in Excel
  • How to count total columns in range in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning