How To Use Concatenate In Excel (And Fix Common Problems!)

Are you looking to join text from multiple cells in Excel? The CONCATENATE function is just what you need. This handy tool allows you to combine text strings, making managing and displaying information easier.

In Excel 2016 onwards, the CONCATENATE function has been replaced with the CONCAT function. Don’t worry; CONCATENATE is still available for backward compatibility, and using CONCAT is just as simple.

In this article, we’ll explore both ways (plus a bonus method) so you can effectively combine text in your spreadsheets.

Understanding Excel Concatenation

Excel concatenation is a powerful way to combine text from different cells. It’s an excellent tool for situations where you need to merge data, like combining names and addresses.

In the past, the CONCATENATE function was used for this purpose. It allowed you to join multiple text strings into one. However, this function has been replaced with the CONCAT function.

Excel also introduced the TEXTJOIN function. It’s similar to CONCAT but offers more flexibility, like adding a delimiter between combined texts.

Here’s an example using:

CONCATENATE: =CONCATENATE(A1, " ", B1)

If cells A1 and B1 contain “Peter” and “Pan” respectively, the result will be “Peter Pan.”

Now let’s use:

CONCAT: =CONCAT(A2, " ", B2)

You’ll see the same result, “Peter Pan.” However, CONCAT offers better compatibility with newer versions of Excel.

With TEXTJOIN, you can include a delimiter. For example:

=TEXTJOIN(" ", TRUE, A4:B4)

The first argument is the delimiter (” “), followed by TRUE (indicating to ignore empty cells) and the range of cells to join (A1). This will also give you “Peter Pan.”

We go into more detail on each function and provide further examples below.

How to Use Concatenate Function

The CONCATENATE Function

As mentioned, the CONCATENATE function in Excel allows you to join strings, making merging text from different cells easier. It is beneficial for combining data, such as first and last names, addresses, or product codes.

The CONCATENATE syntax is:

=CONCATENATE(text1, [text2], ...)

Where text1, text2, etc. are the text items or cell references you wish to combine. You can have up to 255 items, up to 8,192 characters.

Practical Examples

Combine Names – Let’s say you have first names in column A and last names in column B. Use the CONCATENATE function to create full names in a new column:

=CONCATENATE(A1, ” “, B1)

Create Email Addresses – Suppose you have employee names in columns A and B; the email domain is @example.com. To create email addresses in column C, use:

=CONCATENATE(A1, “.”, B1, “@example.com”)

Alternatives to Concatenate Function

The CONCAT Function

The CONCAT function is the modern way to concatenate strings in Excel. The syntax is similar to CONCATENATE:

=CONCAT(text1, [text2], ...)

Key Difference Between CONCAT and CONCATENATE

Besides the version compatibility, there is one key difference.

CONCAT supports array ranges. This means you can specify a range of cells, and it will concatenate all of them.

This makes it more versatile, e.g., =CONCAT(A1:C1) vs =CONCATENATE(A1, B1, C1).

Why is CONCAT not working in Excel?

If CONCAT isn’t working in Excel, you could be using an older software version. CONCAT is available in Excel 2016 and newer versions. Instead, use the CONCATENATE function or the ampersand (&) operator.

The TEXTJOIN Function

Another alternative is the TEXTJOIN function, which allows you to specify a delimiter and combine multiple text strings. It’s even more flexible than CONCAT!

=TEXTJOIN(" ", TRUE, A1:A5)

In this example, the function combines values from cells A1 to A5 with a space as a delimiter. The TRUE argument tells Excel to ignore empty cells.

Advanced Concatenate Tips

With the syntax out of the way, here are a few ideas on concatenating data in Excel.

Concatenating Dates

When you must combine date values in Excel, use the TEXT function. This helps retain the date format during concatenation. For example:

=CONCAT(A2, ” “,TEXT(B2, “mm/dd/yyyy”)

This formula joins the text in cell A1 with the date in cell B1, formatted as mm/dd/yyyy.

Concatenating Numbers

To concatenate numbers with text strings, use the TEXT function again. It keeps the number format intact. For example, to join a numerical value with a percentage:

=CONCAT(A1, ” “,TEXT(B1, “0.00%”))

Here, the number in cell B1 is shown as a percentage with two decimal places.

Conditional Concatenation

Combine the IF function and concatenation for conditional concatenation. For instance, if you need to concatenate values in columns A and B only if the value in A is greater than 50:

=IF(A1>50, =CONCAT(A1,B1), “”)

The logical functions guide can help you explore more conditions like this.

Using Concatenation with LEFT, RIGHT, and MID

LEFT, RIGHT, and MID functions extract specific text segments in a cell. With concatenation, you can combine these segments.

For example, to join the first three characters from cell A1 and the last two characters from cell B1:

=CONCAT(LEFT(A1, 3), RIGHT(B1, 2))

This outputs a new string, merging the specified text parts.

Problems with CONCATENATE in Excel

Here are a few common issues when trying to concatenate in Excel.

Overlooking cell formats

Sometimes, you encounter issues when combining text and numeric data. It’s important to ensure you format your cells correctly before using CONCATENATE. For example:

=CONCATENATE(“The total is “, A2)

Excel will not display the currency symbol if A1 contains a number formatted as currency. To fix this, use the TEXT function to format your numeric data:

=CONCATENATE(“The total is “, TEXT(A1, “$#,##0.00”))

Forgetting delimiters or separators

Another common mistake is forgetting to add delimiters (spaces, commas) between the concatenated elements. Use quotation marks and commas to separate your values:

=CONCATENATE(“Today is “,A2,”,”,A3)

While debugging your formula, remember to check for common issues such as missing operands, cell formats, or incorrect ranges.

Not accounting for empty cells

When CONCATENATE encounters empty cells, it might produce unexpected results. To handle this, use the IF or IFERROR functions to concatenate cells conditionally:

=CONCATENATE(IF(A1=””, “EMPTY”, A1), ” – “, IF(A2=””, “EMPTY”, A2))

Tips and Best Practices

Ensuring Data Consistency

To ensure consistent results, you must clean up your data before concatenation. One common issue is the presence of unnecessary spaces. Excel provides the TRIM function, which helps remove extra spaces.

For example, suppose you have two cells:

A1: John

A2: Doe

If extra spaces exist in either cell, simple concatenation (like =A1 & ” ” & A2) might produce John Doe with too many spaces. Using TRIM, you can fix this issue:

=CONCAT(TRIM(A2), ” “, TRIM(B2))

This formula ensures that there’s only one space between the words.

Handling Large Datasets

When working with large datasets, performance can be a concern. Smooth concatenation in big Excel files requires careful planning. Here are some tips to help:

• If possible, avoid using volatile functions like INDIRECT, which can slow down calculations.
• Ensure you only perform concatenation steps that are needed to save processing time.
• Turn off automatic calculation (Formulas > Calculation Options > Manual) when performing multiple concatenations to avoid unnecessary recalculations.

Remember to keep your formulas concise and straightforward to optimize performance. If it’s still running slow, check out our list of tips to speed up Excel formulas.

Frequently Asked Questions

Related Posts:

• Mastering Excel Formulas and Functions
• What Is The Order Of Operations In Excel Formulas?