Microsoft is back with some new functions for Office 365 subscribers including CONCAT, TEXTJOIN, IFS, SWITCH, MAXIFS, and MINIFS. They’ve also included a new type of chart the “funnel chart”. At least two of these calculation functions are vast improvements over existing functionality.
CONCAT
My clear favorite so far. Ever need to CONCATENATE more than one cell? The old syntax required a daisy chain of commas and cell ranges – now CONCAT can do ranges:
=CONCATENATE(A1,A2,A3,A4,…,A10) is the same as =CONCAT(A1:A10)
My carpal tunnel syndrome thanks you Microsoft.
TEXTJOIN
I will probably be using this quite a bit besides CONCAT. As you know CONCATENATE tends to smash things together without any formatting like spaces or commas. TEXTJOIN makes your life simple. The syntax for the function is =TEXTJOIN(delimiter, ignore_empty, text1…). You can specify that you want to separate each value with either a space or a comma and a space. Microsoft can fill you in more here.
The TEXTJOIN() function combines text from multiple ranges with the added flexibility of a specified delimiter. This function uses the following syntax:
TEXTJOIN(delimiter, ignoreempty, text1, [text2], ...)
where delimiter is a text string or a reference to a text string that represents the character(s) you want to insert between each of the strings you're combining. In addition, ignoreempty is TRUE or FALSE with TRUE (the default) ignoring empty cells. The two text arguments are literal string values or references to ranges that contain the text you want to concatenate.
The biggest advantage is the ability to set the delimiter once. Figure A illustrates this point. Columns F, G, and H contain the following formulas, respectively:
=C2& " for " & D2
=CONCATENATE(C2, " for ", D2)
=TEXTJOIN(" for ",,C2, D2)
In such a simple example, none of the functions appears to be superior because you specify the delimiter only once. But when combining several strings, TEXTJOIN() is more efficient. It also makes it easy to create a string array from a single field—functionality that's been a long time coming. Figure B shows the following function combining all the text values in the Personnel column:
=TEXTJOIN(", ",,C2:C33)
Use CONCAT() to combine multiple strings or ranges similar to TEXTJOIN(), but without the flexibility of a delimiter or empty argument. This function replaces CONCATENATE() (see #1), which remains available for backward compatibility. CONCAT() uses the following syntax:
CONCAT(text1, [text2], ...)
where both arguments are a string or an array of strings, such as a range. You can add a delimiter as an argument, but a better choice would be to use TEXTJOIN(). I'm not sure why Microsoft bothered with this one; I'm including it to be comprehensive and as a gentle warning to start using it instead of CONCATENATE().
added TEXTJOIN and CONCAT to combine a list or range of text strings, MAXIFS and MINIFS to find the maximum or minimum in a range given one or more conditions and IFS and SWITCH to avoid messy nested IF functions. Read on for details on each function.
Combine text strings using TEXTJOIN and CONCAT
A very common task for users in spreadsheets is to combine text strings, but until now, if you wanted to join text strings from a range of cells, you had to specify each cell individually. The new TEXTJOIN and CONCAT functions let you combine text strings from ranges of cells with or without using a delimiter, such as a comma separating each item. You can simply refer to the range and specify the delimiter once and let Excel do all the heavy lifting. Concantenation.
The old-fashioned way:
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)
The new way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)
Let’s say you just want to join the parts of an address into a single text string. The old way would require you to specify each cell and repeat a comma that separates each part:
The new way is much simpler. You simply specify the comma (or whatever separator you want), choose whether to ignore empty cells and then specify the range.
If you want to know more, see the online help for TEXTJOIN and CONCAT.
For a long time CONCATENATE has been the first function we thought of when we needed to combine text in Excel. A couple of new players has entered the game recently and has shaken the things up a bit. Yes, I'm talking about TEXTJOIN and CONCAT designed to help you combine a range of strings. Use these simple functions whenever you need to merge parts of names, addresses, or phrases, combine numbers and words. Currently they are available in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.
TEXTJOIN function in Excel
CONCAT function in Excel
Concatenate strings in Excel
The TEXTJOIN function - description and syntax
The TEXTJOIN function in Excel concatenates text from multiple ranges or strings. You can specify a delimiter to include between each text value and ignore empty cells. Even if the delimiter is an empty string, TEXTJOIN will successfully join text in Excel.
Here's how the function looks like:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
It has 3 required and 1 optional arguments:
The delimiter lets you specify any character for separating your text. This may be a comma, space, ampersand, or anything you like. The characters must be enclosed by double quotes, or a reference to a valid text string. Any numbers will be regarded as text.
Ignore_empty is a required argument. If it's TRUE, empty cells will be ignored. You can include blank cells by setting this value to FALSE.
text1 lets you specify a range of cell values to concatenate rather than having to enter each one.
[text2, …] is an optional argument that allows specifying further ranges to include in if your range is not continuous.
There can be a maximum of 252 arguments for the text items. If the resulting string exceeds the cell limit of 32767 characters, the TEXTJOIN function returns the #VALUE! error.
The CONCAT function in Excel - description and syntax
Introduced as part of the update in February 2016, CONCAT replaces the CONCATENATE function in Excel. It works exactly the same way. Looks like it has been added to reduce the length of the function name. Also, CONCAT is the standard function used by Google Sheets. CONCATENATE continues to be supported to provide compatibility with the previous versions of Excel.
The structure of the function is as follows:
CONCAT(text1, [text2],…)
CONCAT has just two arguments one of which is optional:
text1 is the text entry to be joined. It can be a string, or a range of strings.
[text2, …] stands for additional text items to be combined.
There can be a maximum of 253 arguments for the text values.
Excel - concatenate strings using the TEXTJOIN and CONCAT functions
Joining text is one of the most common tasks in Excel. In the previous versions if you needed to concatenate text from several cells, it was necessary to specify each one individually. With the new TEXTJOIN and CONCAT functions, you can simply refer to a range and combine text strings from cell ranges with or without a delimiter.
The main limitation of the CONCATENATE() function that CONCAT() has addressed is that we can now select a range of cells to join while previously were limited to listing cells separately. Say, you want to combine parts of telephone numbers. With the Concatenate function, you will need to enter the cell addresses one by one.
=CONCATENATE(A1,B1,C1,D1,E1,)
Combine strings using the CONCATENATE function]
In this case, CONCAT looks much more compact. All you need to do is just pick the range with the Excel strings to concatenate.
=CONCAT(A1:E1)
Join numbers with the help of CONCAT]
At the same time, both CONCAT and CONCATENATE look the same when you need to join text using delimiters.
=CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2)
How to use the Concatenate function in Excel]
=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)
Use the CONCAT function to combine text in Excel]
As it can be easily seen, the functions don't process empty cells which results in extra spaces.
In this case, TEXTJOIN is destined to be a really popular choice for users working with large amounts of text data. You simply specify the space delimiter, choose to ignore empty cells and define the range. This is by far a smarter solution.
=TEXTJOIN(" ",TRUE,A2:E2)
Use the TEXTJOIN function to concatenate text in Excel]
If you have Excel 2016, do explore the TEXTJOIN and CONCAT functions to see how helpful they are and say bye-bye to concatenated and nested if statements when combining strings.
No comments:
Post a Comment