Quadra

Connecting Technology and Business.

Forget CONCATENATE and the Clutter

Combining text strings has become easier using TEXTJOIN in Excel 2016 of Office 365 ProPlus and is available for subscribers right-away. (You must have the latest version of Office installed in your PC). This is also available for users using Excel Online, Excel Mobile and Excel for Android phones and tablets.

 

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 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. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

 

The old-fashioned way:

 =CONCATENATE(A3, β€œ, β€œ, B3, β€œ, β€œ, C3,”, β€œ, D3, β€œ, β€œ, E3)

 

The new way to join text strings using TEXTJOIN:

 =TEXTJOIN(β€œ, β€œ, TRUE, A3:E3)

 

TEXTJOIN has three arguments – (1) the delimiter to be used, (2) whether to ignore empty cells and (3) the range in which the text strings are located.

 

So, you simply specify the comma (or whatever separator you want), choose whether to ignore empty cells (True) or include empty cells (False) and then specify the range. If a number is supplied, it will be treated as text.

 

For example, =TEXTJOIN(" ",TRUE, "The", "sun", "will", "come", "up", "tomorrow.") will return The sun will come up tomorrow.

 

There can be a maximum of 252 text arguments for the text items, including the first text. Each can be a text string, or array of strings, such as a range of cells. If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.

 

Here is another example:

 

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.

 

 

 

Note:


There is also a similar and improved function CONCAT. This CONCAT replaces the CONCATENATE function though the CONCATENATE function will still be available for backward compatibility.

 

The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or Ignore Empty arguments.

 

For Example, =CONCAT("The"," ","sun"," ","will"," ","come"," ","up"," ","tomorrow.") will return The sun will come up tomorrow.

 

There can be a maximum of 253 text arguments for the text items. Each can be a string, or array of strings, such as a range of cells. If the resulting string exceeds 32767 characters (cell limit), CONCAT returns the #VALUE! error.

Loading