Excel CONCATENATE function to combine strings, cells, columns

This article explores various methods for combining text strings, numbers, and dates in Excel using the CONCATENATE function and the "&" operator. We'll cover formulas for joining individual cells, columns, and ranges, offering solutions for tasks like combining names and addresses, merging text with calculated values, and formatting dates and times.

Different Approaches to Excel String Concatenation

Excel offers two primary ways to combine data: merging cells and concatenating cell values. Merging cells physically joins cells, creating a larger cell spanning multiple rows or columns. Concatenation, however, combines only the contents of cells, joining text strings or inserting calculated values within text.

The CONCATENATE Function

The CONCATENATE function joins text strings or cell values. Its syntax is CONCATENATE(text1, [text2], ...) where text represents a text string, cell reference, or formula result. For example, =CONCATENATE(A1,", ",B1) combines the contents of cells A1 and B1 with a comma and space. Excel 365 and later also offer the CONCAT function, functionally identical but potentially more efficient.

Key Considerations for CONCATENATE

• Requires at least one text argument.
• Supports up to 255 strings, totaling 8,192 characters.
• Always returns a text string, even with numeric inputs.
• Unlike CONCAT, it doesn't handle arrays; each cell reference must be separate.
• Invalid arguments result in a #VALUE! error.

The Ampersand (&) Operator

The ampersand (&) provides a concise alternative to CONCATENATE. For instance, =A1&" "&B1 achieves the same result as the previous CONCATENATE example.

Concatenation Formula Examples

• Joining cells without separators: =CONCATENATE(A1,B1) or =A1&B1
• Adding delimiters: =A1&", "&B1 (comma and space)
• Combining text and cell values: =CONCATENATE("Order ",A1," is complete.")
• Incorporating formulas: ="Today is "&TEXT(TODAY(),"mmmm dd, yyyy")
• Adding line breaks (Windows): =A1&CHAR(10)&B1
• Concatenating columns: Apply a formula like =A1&" "&B1 to the first row and drag down.

Working with Ranges

CONCATENATE doesn't directly handle ranges. For multiple cells, list each reference individually (e.g., =CONCATENATE(A1,A2,A3)). Excel 365's CONCAT function simplifies this for ranges (=CONCAT(A1:A3)).

Combining Text and Formatted Numbers

Use the TEXT function to control number formatting within concatenation. For example, =A1&" $"&TEXT(B1,"0.00") combines text with a currency-formatted number.

The Merge Cells Add-in

The Merge Cells add-in (part of suites like Ultimate Suite for Excel) offers a user-friendly, formula-free method for concatenating cells with various delimiters, including line breaks. It provides options for merging cells, rows, or columns.

Opposite of Concatenation: Splitting Cells

To reverse concatenation (split cells), utilize Excel's "Text to Columns" feature, Flash Fill (Excel 2013 ), the TEXTSPLIT function (Excel 365), or custom formulas using functions like MID, RIGHT, and LEFT.

Note: Image URLs are retained from the original input. I cannot display images directly.

The above is the detailed content of Excel CONCATENATE function to combine strings, cells, columns. For more information, please follow other related articles on the PHP Chinese website!