#textjoin for older excel

[tumblingwavesoftware]

How to join cells with formatting

How to join cells with formatting

The best way way to join cell ranges in Excel is to use the CONCATENATERANGE formula. It gives you the option to format the results, and works with ALL versions of Excel. It is a better alternative to other formulas like TEXTJOIN, CONCAT and CONCATENATE. Here is a demo of the CONCATENATERANGE formula.

[professorexcel]

Combine Text in Excel: 4 Best Ways (+Download)

Excel offers three distinct formulas as well as a fourth way to combine multiple text cells into one cell. There are countless examples in which you might need this: Combine given– and family names or preparing primary keys for multi-conditional lookups. For example, in a VLOOKUP or INDEX/MATCH formula combination. In this article you learn 4 methods and in the end, you learn how to deal with a large range of cells.

Method 1: CONCAT formula

Example for the CONCAT formula.

The CONCAT formula has been introduced to Excel with the version 2016. It’s not available on previous versions of Excel. And that’s already the biggest disadvantage. Besides that, this formula is very useful.

The CONCAT ist he sucessor oft he CONCATENATE formula and has at least one and at maximum 254 arguments. It can handle separate cells as well as cell ranges. It’s even possible to combine single cells with cell ranges, e.g.

=CONCAT(B4,C4:D4)

 .

As you can see in the screenshot above, using the CONCAT formula is very easy. Just refer to the cells you’d like to combine.

.aq_block_15138 h1 { color: ; } .aq_block_15138 p { color: ; }

(adsbygoogle = window.adsbygoogle || []).push({});

  Method 2: CONCATENATE formula

Example for the CONCATENATE formula.

Unlike the CONCAT formula, CONCATENATE is available in older versions of Excel. Microsoft says within the help section of the CONCATENATE formula, that the CONCATENATE is replaced by CONCAT. CONCATENATE is only kept in Excel in order to guarantee the compatibility to older versions on Excel and it’s recommended to use CONCAT instead. CONCATENATE works almost the same way like CONCAT with one major difference: It’s not possible to use ranges of cells as references. Only single cells can be combined. The maximum number of arguments and therefore single cell references is 255.

  Method 3: TEXTJOIN formula

Structure of the TEXTJOIN formula.

Since Excel 2016 there is another, advanced option to combine text in Excel. The formula is called TEXTJOIN. Besides simply putting text together, the formula offers two advanced options:

You can define a separator between each cell you want to combine, for example a comma.

The formula provides the option to automatically skip blank (empty) cells.

The structure of the TEXTJOIN formula is shown in the figure above. The formula has at least three arguments.

Delimiter: The letter or word is added in-between and separates each cell input. A common delimiter is a comma.

Ignore empty: The second argument is either TRUE or FALSE. If set to TRUE or skipped, blank (empty) cells won’t be regarded. If set to FALSE, empty cells will also be regarded and separated by the delimiter.

The TEXTJOIN formula requires at least one argument for the cells or text to be combined. This can be a cell range, a single cell or a value. It’s possible to use up to 252 references.

Example of the TEXTJOIN formula.

The screenshot of the example on the right-hand side shows an example for the TEXTJOIN formula. The first part is skipped which means that there is no delimiter. The second argument is set to FALSE so that blank cells aren’t ignored. Eventually the last argument refers to the cell range B4 to D4 which contains the cells to be combined.

  Method 4: &-sign

Example of the &-sign to combine values.

The easiest way is probably to just use the “&”-sign to combine values in Excel. This method has the same disadvantages like the CONCATENATE formula from the method 2 above. It can only regard single cells and not ranges of cells. An advantage of this method is that it’s usually easier to follow up the calculation steps.

As you can see in the screenshot above, you can just refer to several cells and combine them with the &-sign. That’s it.

  .aq_block_23554 h6 { color: #ff6200; } .aq_block_23554 p { color: ; }

Hold on a second. Was this information helpful so far?

.aq_block_53554 h3 { color: #2b803f; } .aq_block_53554 p { color: ; }

If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:

.aq_block_63554 h1 { color: ; } .aq_block_63554 p { color: ; }

The best Excel tips, tricks and tutorials. 1x per month. No spam. Promised.

.aq_block_93554 h1 { color: ; } .aq_block_93554 p { color: ; }

Your welcome gift: Our big 45 pages keyboard shortcuts package.

.aq_block_123554 h1 { color: ; } .aq_block_123554 p { color: ; }

Subscribe now!

.aq_block_133554 h1 { color: ; } .aq_block_133554 p { color: ; }

#mc_embed_signup{clear:left; font:14px Helvetica,Arial,sans-serif; } /* Add your own MailChimp form style overrides in your site stylesheet or in this style block. We recommend moving this block and the preceding CSS link to the HEAD of your HTML file. */

(function($) {window.fnames = new Array(); window.ftypes = new Array();fnames[0]='EMAIL';ftypes[0]='email';fnames[1]='FNAME';ftypes[1]='text';}(jQuery));var $mcj = jQuery.noConflict(true);

Learn more

  Example: Combine many cells in Excel

Let’s say, you want to combine 1,000 Excel cells into one cell. The good news: You can use all four methods to accomplish this, even in a simple manner. There is one restriction though: One Excel cell can’t contain more than 32,768 characters.

For combining 1,000 cells in Excel, you can use two basic approaches:

Example of how to combine 1,000 cells using the CONCAT formula.

Use method 1 (CONCAT formula) or method 3 (TEXTJOIN formula) above which can regard cell ranges. The solution using the CONCAT formula is shown in Figure 66. As noted before, the only requirement of this method is that you have to use Excel version 2016.

When using the &-sign for combining 1,000 cells, the easiest way is to insert a helper column.

Insert a helper column (or row, depending on how your data is organized) which always combines the current cell with the previous combination of all cells so far. As you can see in the image on the right-hand side, the helper column (here in cell G6) combines cells G5 (which contains the previous combination) with the current cell F6. The solution in cell I3 only links to the last row in the helper column.

  Download

All the examples above can be found in this example workbook.

Please click here and the download starts immediately.

  .aq_block_15336 h1 { color: ; } .aq_block_15336 p { color: ; }

Was the information helpful in this article?

If yes, why don't you subscribe to our free, monthly Excel newsletter?

If no, please let us know what to improve.

  .aq_block_15138 h1 { color: ; } .aq_block_15138 p { color: ; }

(adsbygoogle = window.adsbygoogle || []).push({});

Der Beitrag Combine Text in Excel: 4 Best Ways (+Download) erschien zuerst auf Professor Excel.

from Professor Excel http://ift.tt/2wLCLmm

[tumblingwavesoftware]

New Version of Concatenate Range!

New Version of Concatenate Range!

Version 2.0 of Concatenate Range is released now, and it includes support for 64-bit Excel! Concatenate Range allows you to join together ranges of cells in Excel. The included formula, CONCATENATERANGE, is a better alternative to the TEXTJOIN and CONCAT formulas, because: you can format the results.you can use it in older versions of Excel, like Excel 2007. Go to the Tumbling Wave Software…