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.
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.
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3,
“, “, E3)
way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)
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.
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.
example, =TEXTJOIN(" ",TRUE, "The", "sun",
"will", "come", "up", "tomorrow.") will
return The sun will come up tomorrow.
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.
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:
way is much simpler.
also a similar and improved function CONCAT. This CONCAT replaces the CONCATENATE
function though the CONCATENATE function will still be available for backward
CONCAT function combines the text from multiple ranges and/or strings, but it
doesn't provide the delimiter or Ignore Empty arguments.
Example, =CONCAT("The"," ","sun","
","up"," ","tomorrow.") will return The sun
will come up tomorrow.
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.