Which Should You Use in Excel?

Manually combining information from two or extra cells into one other cell would take time and inevitably end in errors, particularly should you’re coping with a lot of information. Excel is designed to automate time-consuming duties, and the CONCAT and TEXTJOIN features are textbook examples of this in motion.

In Brief: CONCAT vs. TEXTJOIN

Each mix textual content from a number of cells into one.

In keeping with Cambridge Dictionary, the verb to concatenate means “to place issues collectively as a linked sequence,” and the CONCAT function does precisely this by merely bringing textual content or cell references collectively right into a steady string:

=CONCAT(a,b,c)

the place a, b, and c are the textual content or cell references you wish to mix into one cell. Enclose any textual content values (together with punctuation and areas) in double-quotes. You may have as much as 252 references or textual content strings right here.

If clean cells are referenced within the CONCAT components, they’re ignored within the output until positioned between commas.

TEXTJOIN does the identical job as CONCAT, however a delimiter means that you can select what to insert between every merchandise within the concatenation. TEXTJOIN additionally provides you the choice to incorporate or exclude empty cells.

=TEXTJOIN(w, x, y)

the place w is the delimiter, x is TRUE if you wish to ignore empty cells (or FALSE if you wish to embody empty cells), and y is the primary of as much as 252 references or textual content strings.

In Observe: CONCAT vs. TEXTJOIN

Let’s look additional into these with some examples.

CONCAT

Within the instance under,

=CONCAT(A1,B1,C1)

combines all the information in cells A1 to C1 into cell B3. We might even have typed

=CONCAT(A1:C1)

because the cells are contiguous, and we did not require any values, punctuation, or areas between the numbers in cell B3.

Here is one other instance:

=CONCAT(A1," ",A2," ",A3)

which might add an area in between every worth pulled from cells A1 to C1. Keep in mind, every argument must be separated by a comma, and any textual content values (together with punctuation and areas) must go in double-quotes.

You would possibly use CONCAT in these situations:

• You’ve folks’s first names in column A, and their second names in column B, and also you wish to mix them (separated by an area) in column C.
• You wish to automate sentences based mostly on cell values. For instance, you may need an individual’s identify in column A and their age in column B, and also you wish to create sentences for every individual that say “[Name] is [age] years outdated” in column C.
• That you must create composite and compound keys, the place you might have two lists of reference numbers and wish to make a 3rd listing that mixes the 2.

TEXTJOIN

Let’s take the identical instance, however this time, we wish to add the phrase “and” between every quantity.

Reasonably than utilizing CONCAT and repeating “and” between every worth, we will kind and as a delimiter (the primary argument contained in the parentheses) with the TEXTJOIN perform.

=TEXTJOIN(" and ",TRUE,A1:C1)

If we had been to make use of the identical components, however take away the quantity 2 from cell B1, here is what we might see:

It is because we typed TRUE because the second argument within the components to inform Excel to exclude clean cells. If we typed FALSE, cell B3 would have learn “1 and and three”.

You would possibly use TEXTJOIN in these situations:

• You’ve an inventory of e-mail addresses on a spreadsheet, and also you wish to be a part of them collectively—separated by commas—to repeat and paste into Outlook for a batch e-mail.
• You’ve got collected a workforce’s outcomes with W, D, and L, and also you wish to compile all their outcomes into one cell, separated by dashes.
• You wish to be a part of massive volumes of knowledge along with areas in between, otherwise you wish to embody clean cells in your joined information.

In Sum: CONCAT vs. TEXTJOIN

So, which one must you use?

If you wish to merely mix values from quite a few cells right into a single cell, you are able to do that with CONCAT with out having to fret about delimiters and clean cells. Simply keep in mind that until you add areas or punctuation manually, all of the values might be squished collectively with out clear definitions between every. You may additionally favor to make use of CONCAT if you wish to have completely different delimiters between every concatenated worth.

TEXTJOIN is healthier for outlining what is going to go in between every linked worth, similar to phrases, areas, or punctuation. Reasonably than utilizing CONCAT and manually inserting a comma, for instance, between every worth, you are able to do this extra effectively by including a delimiter as the primary argument contained in the parentheses. That is notably useful should you’re dealing with massive quantities of knowledge. Simply keep in mind that you could solely specify one delimiter when utilizing TEXTJOIN, that means you may find yourself with the identical worth or image positioned between every merchandise joined collectively.

In addition to these described above, there are different methods to hitch values collectively in Excel. First, you need to use the ampersand (&) image between cell references in a components. Alternatively, Excel’s Flash Fill tool (keyboard shortcut: Ctrl+E) spots patterns and applies them to the remainder of the sequence.