Excel: Combine Three Columns into One

For efficient data preparation, two robust functions in Excel are commonly employed to merge content from multiple cells into a single output:

Why Data Consolidation is Essential in Excel

Data consolidation, often referred to as concatenation when dealing with text strings, is far more than just a formatting exercise; it is a critical step in preparing raw data for further processing, analysis, or integration with other systems. In many business environments, data arrives in fragmented formats. For instance, customer records might separate address components (street number, city, state, zip code) into distinct columns, or inventory systems might list product details (SKU, color, size) individually. While this structure is useful for inputting data, it becomes inefficient for reporting, presentation, or creating unique identifiers.

The core challenge addressed by column combination is creating standardized identifiers or unified descriptions. Imagine needing to generate a custom email address list or a mailing label where all components must reside in a single field. By merging three or more columns, we ensure that related data elements are logically grouped, making the resulting dataset cleaner and more usable. Furthermore, many external applications or database systems require concatenated fields (e.g., a full name field instead of separate first and last name fields) before they can successfully import the information.

Utilizing specialized Excel functions for this task ensures accuracy and scalability. When dealing with hundreds of thousands of rows, manual manipulation is impossible. The programmatic approach offered by functions like CONCAT or TEXTJOIN provides a dynamic solution: if the source data in columns A, B, or C changes, the combined result in column D updates instantly, maintaining data integrity across the spreadsheet. This automation capability saves significant operational time and reduces the likelihood of discrepancies often introduced by manual data entry.

Method 1: Using the CONCAT Function for Simple Joining

The CONCAT function is one of the most straightforward methods for joining text strings or values from multiple cells into one. Introduced in Excel 2016 as a replacement for the older `CONCATENATE` function, `CONCAT` offers a streamlined syntax, particularly its ability to accept a range of cells, making it ideal for combining adjacent columns quickly. This function is perfectly suited for situations where no separator (like a space or a comma) is required between the combined elements, resulting in a single, unbroken string.

When applying `CONCAT`, the user simply specifies the cells or range of cells they wish to merge. It treats all specified contents—whether text, numbers, or dates—as text strings and joins them end-to-end in the order they appear in the formula or the specified range. It is crucial to remember that `CONCAT` does not automatically insert spaces or any other characters. If spacing is necessary, it must be manually included within the formula by explicitly referencing a space character enclosed in quotation marks, though this diminishes the efficiency gained by using the range feature.

The syntax for using CONCAT across a contiguous range, such as combining three columns (A, B, and C) for a single row (row 2), is remarkably clean and brief.

The basic structure is:

Method 1: Use CONCAT Function

=CONCAT(A2:C2)

This specific formula instructs Excel to combine the values extracted sequentially from cells A2, B2, and C2. The result will be a single text string placed in the output cell, with absolutely no separator or space inserted between the merged components. This is perfect for generating unique identifiers or codes where gaps are undesirable.

Practical Example 1: Implementing CONCAT

To illustrate the power of CONCAT, let us consider a practical dataset used by a sales team. This dataset tracks employee sales performance, separating the Employee ID (Column A), Product Code (Column B), and Total Sales Value (Column C). For internal reporting or database integration, we might be required to create a unique transaction key by merging these three fields without any intervening characters.

The following dataset contains the information we intend to consolidate. Notice how the data is segregated into distinct columns, which we aim to unify into a single, comprehensive column D for easy tracking and reference.

The following table represents the sample data detailing employee sales activity:

To begin the process of data unification, we will select cell D2, which will house the first concatenated result. We then input the `CONCAT` formula, referencing the range A2 through C2. This range encapsulates all the data points we wish to combine for the first employee record.

We type the following formula into cell D2 to combine the values in cells A2, B2 and C2 with no space between the values:

=CONCAT(A2:C2)

Once the formula is entered in D2, we can swiftly apply this logic to the entire dataset. By clicking and dragging the formula down using the fill handle to the remaining cells in column D, Excel automatically adjusts the row references (A3:C3, A4:C4, and so on), ensuring every record is correctly processed.

We then click and drag this formula down to each remaining cell in column D:

The final output in Column D successfully merges the Employee ID, Product Code, and Sales Value into a single, seamless cell. Notice that because CONCAT was used without explicit separators, the resulting strings are tightly packed, fulfilling the requirement for a continuous unique key.

Method 2: Leveraging TEXTJOIN for Delimited Concatenation

While CONCAT is efficient for simple joining, most real-world applications require a delimiter—a character like a space, comma, or dash—to separate the combined data elements, enhancing readability and parsing capabilities. This is where the powerful TEXTJOIN function excels. Introduced alongside `CONCAT`, TEXTJOIN is specifically designed to concatenate ranges of text strings with the added flexibility of a user-defined separator.

The syntax of the TEXTJOIN function includes three crucial arguments. First is the delimiter, which is the character or string (enclosed in quotes) that will be inserted between each cell’s content. Second is a logical value (TRUE or FALSE) indicating whether to ignore empty cells; setting this to TRUE is highly recommended for creating clean outputs. Third is the range of cells to be joined. This structure allows for superior control over the output format compared to simpler concatenation methods.

Using TEXTJOIN is the professional standard for tasks such as constructing mailing addresses, creating human-readable employee names (First Name [space] Last Name), or generating comma-separated value (CSV) strings directly within Excel. Its ability to handle multiple cells across a range and automatically apply the separator minimizes formula complexity, especially when combining many columns, solidifying its place as a cornerstone function in advanced data preparation.

The structure for joining three columns using a standard space as the separator is as follows:

Method 2: Use TEXTJOIN Function

=TEXTJOIN(" ", TRUE, A2:C2)

Here, " " specifies the space delimiter, and TRUE ensures that any blank cells within the range A2:C2 are ignored, preventing unnecessary extra spaces in the output.

Practical Example 2: Implementing TEXTJOIN with Spaces

Returning to our sales data example, suppose the requirement shifts from creating a machine-readable unique key to generating a human-readable summary description. In this case, we absolutely need spaces between the Employee ID, Product Code, and Total Sales Value to ensure clarity. We will utilize the TEXTJOIN function, specifying a space (" ") as our required separator.

We begin by inputting the formula into cell D2. The formula specifies that a space should be used as the separator (the first argument), that empty cells should be ignored (TRUE, the second argument), and that the data range includes A2, B2, and C2 (the third argument). This configuration is optimized for text readability and robust data handling.

We type the following formula into cell D2 to combine the values in cells A2, B2 and C2 with a space between the values:

=TEXTJOIN(" ", TRUE, A2:C2)

After entering the formula in the initial cell, the remaining records are processed by dragging the formula down column D using the fill handle. This action populates the entire column with the concatenated text strings, automatically inserting the space delimiter between the contents of each source column for every row.

We then click and drag this formula down to each remaining cell in column D:

As visible in the output, Column D now contains the consolidated values from columns A, B, and C, separated clearly by a space. This makes the data immediately understandable and useful for reporting summaries or display purposes, showcasing the fundamental advantage of using TEXTJOIN over `CONCAT` when separators are mandatory.

Advanced Delimiters with TEXTJOIN

The versatility of the TEXTJOIN function extends far beyond simply inserting spaces. The first argument of the function, which specifies the delimiter, can be any character, symbol, or string of text enclosed in quotation marks. This functionality is crucial for compliance, database exports, or generating specific file formats such as CSV (using a comma) or pipe-delimited files.

For instance, if we needed to structure the data so that it could be easily parsed later, perhaps separating the numerical codes with a dash (-) would be more appropriate than a space. This creates a visually distinct separation without relying on whitespace, which can sometimes be ambiguous. To implement this, we simply replace the space character (" ") in the formula with a dash ("-").

We could use the following formula, for example, to combine the values from columns A, B and C with a dash in between the values:

=TEXTJOIN("-", TRUE, A2:C2)

This minor adjustment in the formula demonstrates the immense control users have over the output format. The formula maintains its efficiency and range-handling capabilities while producing a structurally different result, allowing for tailored data outputs based on specific project or system requirements.

The following screenshot shows how to use this formula in practice:

Column D now combines the values from columns A, B and C into a single cell with a dash in between the values. This is particularly useful when creating standardized identifiers where parts of the ID need to remain distinct but combined into one field, such as SKU-COLOR-SIZE combinations.

Key Differences and Selection Criteria

While both CONCAT and TEXTJOIN achieve the fundamental goal of merging columns, they serve different operational needs, and choosing the right one is key to efficient data manipulation. The primary distinction lies in the handling of separators and blank cells. `CONCAT` is a direct merger tool, gluing everything together without intervention and requiring complex workarounds if separators are needed.

In contrast, TEXTJOIN is fundamentally designed for structured merging. It mandates the definition of a separator (the delimiter) and offers an optional, yet highly valuable, argument to ignore empty cells. The ability to automatically disregard blanks is especially important when dealing with imperfect datasets where some records might be missing data in one of the source columns; `TEXTJOIN` prevents the appearance of unwanted double delimiters or awkward gaps in the final output.

When deciding which function to use, consider the desired final format:

1. If the goal is to create a unique, unbroken identifier (e.g., a serial number or an access key) and no spaces or symbols are allowed, use CONCAT for maximum efficiency.
2. If the goal is to enhance readability, prepare data for reporting, or export to a structured format (like CSV), requiring spaces, dashes, commas, or any other specific character between elements, always use TEXTJOIN.

Understanding these nuanced differences ensures that data experts can select the most robust and elegant solution for any column consolidation task they face in Excel.