All the school and college-going students, full-time working in offices, freelancers, and almost all the fields of work and education use MS Excel for keeping records and tabs of inventory and other details. So it is very important to know the fundamental features and shortcuts of MS Excel to be quick, efficient, and accurate about the work that is being done on it. One such feature of MS Excel is TEXTJOIN which allows the users to combine a table or series of text strings into one string with the help of a delimiter. The text join function can be performed on both blank and filled cells.
The text join feature was first included in the 2019 version of MS Excel and has changed the way people used to merge or combine multiple strings into one. Hence, learning how to use it and applying it while working is the need of the hour for everyone. This post features three simple, easy, and quick ways for using the excel textjoin alternatives for the Concat function.
Components of the TEXTJOIN function
The textjoin function includes syntax and an argument that simplifies the data and merges multiple data strings into a single string.
- Syntax of the textjoin function-
- The argument of the textjoin function-
The three main components of the argument of the textjoin function are “textjoin multiple delimiters, ignore_empty, and text1.” The delimiter tells the software which chain of texts are to be separated, the ignore_empty provides information about including or ignoring the empty cells in the specified series of strings, and the text1 guides MS Excel to the first string that needs to be joined.
The maximum number of texts combined using the textjoin feature is 252. This series can extend like text1, text2, text3, ….text252. The argument of the textjoin function can be both text and numbers.
Method 1: TEXTJOIN Function with Single Cells
Suppose in a classroom of 15 students, the data about the roll numbers, names, and marks scored in a particular subject are noted in a list. To make the data more accessible and easy to read, you want to combine this data in a single cell. This combining of data can be done with the help of the textjoin function with the roll number, name, and marks obtained by the student in the same cell separated by commas(,) in an instant.
The formula for combining this information into a single cell is-
Click on the cell you want the merged data in and enter this formula.
=TEXTJOIN(“, “,TRUE, the cell number of the cells whose data needs to be joined.)
Example- If the numbers or text in the cell numbers B4, C4, and D4 are to be merged, you will have to enter the “=TEXTJOIN(“, “,TRUE,B4,C4,D4)” formula in Excel.
Method 2: TEXTJOIN Function with Multiple Values
The textjoin function can also combine multiple values or a series of values into one cell. For instance, in the data given in the previous section, you want to merge the names of the first ten or the top 10 students in the class. This series of values can be put together in a single cell using the textjoin feature in the following way.
Step. 1→ First, click on the cell in which you want the merged data.
Step. 2→ Enter the given formula-
=TEXTJOIN(“, “,TRUE,cell numbers assigned to the cells of the names of the first ten students.)
Example: The names of the first ten students are in column number C and start from the 4th row of the Excel spreadsheet. Then, the formula for merging this data into one cell will be-
Method 3: TEXTJOIN Function with Another Function
The textjoin function can also be used alongside other functions in MS Excel to save a lot of time and effort. The feature most widely used with textjoin is the “Filter” function. The “Filter” function filters or sorts out the required information from the given set of data.
Following is the data set with Year, Host, Winner, and Runner-up of all the cricket world cups held to date.
|India and Pakistan
|Australia and New Zealand
|Pakistan and India
|India and Bangladesh
|Australia and New Zealand
|England and Wales
Hence, to use the Filter and the textjoin functions together to get the return value as the years in which Australia won the cricket world cup in a single cell. This method shows how to use excel textjoin if multiple criteria.
Step. 1→ Use the filter function by typing the given formula-
B4 is the cell number containing the first year of the cricket world cup, and B14 includes the year in which the next world cup will be held. Similarly, the cells from D4 to D13 contain the names of the cricket world cup winner. After performing this function, you will get an array of the years in which Australia won the cricket world cup.
Step. 2→ Then, use the textjoin function to merge this array of data into a single cell segregated by commas(,). Type the formula given here-
The textjoin function can also be similarly used with other array functions to ease the sorting out of data and preserve time and effort to increase work efficiency.
The textjoin function can be the savior for numerous people working in data handling, data entry, managing inventories of items, etc. This feature works pretty well with other functions of MS Excel to textjoin multiple delimiters. Using textjoin, you can merge data in single cells with various values and incorporate it with other functions of MS Excel. Therefore, we suggest you learn how to use this function properly to make the most of it and let the software do most of the work for you.
How Do I Determine Which Version of Excel I Have?
If you have a problem with Why is my Textjoin formula not working?, then you must check your Excel Version. The Way to check your Installed Excel version is different for Mac and Windows PC users. here’s how to check on Both systems.
- On Mac:- Open the Excel Document [Blank or Other], From the top menu Word > About Excel. See the Version and Subscription that is active on your MS Office account.
- On Windows:- Open Office Application or Document, Select Account and Under the Product Information, See the Product name and Version.
Why can’t I see Textjoin in Excel?
→ The textjoin function of Excel is available in MS Excel 2019 and later versions of the software. If you have an older version or any other version of MS Excel installed apart from these, you won’t be able to see the textjoin function in it.
Is Textjoin the same as a concatenating function?
→ The CONCAT function primarily puts all the values together in a single cell without offering any added options or choices like using it with other functions. On the other hand, the TEXTJOIN function allows the users to combine the values assigned to the delimiter of their choice and features an extra option to ignore empty values. That’s the basics of What is the difference between concat and Textjoin?