How to Combine Columns in Excel: Multi-Column Methods Guide

Combining columns in Excel is one of the most common data preparation tasks. Whether you are merging first, middle and last name into a full name field, combining address components into a single shipping address, or assembling product codes from multiple part numbers, the underlying operation is the same — concatenate values from several columns into a single output column.

Excel offers several functions and tools for this purpose, each suited to slightly different scenarios. The right choice depends on how many columns you are combining, what separator you need, how blank cells should be handled and whether you want a static or dynamic result.

For combining 3 or more columns specifically, the modern best practice is the TEXTJOIN function. TEXTJOIN takes a delimiter as the first argument, an ignore_empty flag as the second argument, and the values to join as subsequent arguments. =TEXTJOIN(" ", TRUE, A2, B2, C2, D2) combines four columns separated by spaces, automatically skipping any blank cells. The function shines for multi-column combinations because it handles the separator logic and blank cells cleanly without nested IF formulas.

Other approaches each have their place. The ampersand operator (&) is the simplest concatenation: =A2 & " " & B2 & " " & C2 produces the same result for three columns but requires explicit separator text repeated between each value. The CONCAT function joins values without separators: =CONCAT(A2, B2, C2) produces unseparated output. CONCATENATE (the older function) and CONCAT both lack TEXTJOIN's blank-handling capability. Flash Fill (Ctrl+E) produces a static result by pattern-matching examples; Power Query Merge Columns produces a dynamic transformation in a query.

This guide covers every method for combining multiple columns in Excel — TEXTJOIN as the modern standard, the ampersand operator for simple cases, CONCAT and CONCATENATE for non-separated joins, Flash Fill for one-time static results, Power Query Merge Columns for analytical pipelines, handling blank cells and separators correctly, preserving leading zeros and number formatting, and the practical decision of which approach fits each scenario. The goal is to know which tool to reach for when combining 3+ columns rather than just 2.

The TEXTJOIN function is the modern best practice for combining 3 or more columns. The syntax is =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...). The delimiter is the separator between values — typically a space, comma, hyphen or similar. The ignore_empty flag is TRUE to skip blank cells (recommended) or FALSE to include them as empty strings. The text arguments can be individual cells, ranges or hardcoded values. TEXTJOIN handles up to 252 text arguments and supports both single cell references and range references.

For typical name combination, =TEXTJOIN(" ", TRUE, A2, B2, C2) produces "John Q Smith" from first name in A2, middle initial in B2 and last name in C2. If middle initial is blank, the result is "John Smith" — TEXTJOIN automatically removes the would-be double space because the ignore_empty flag skips the blank. The same formula handles edge cases (no middle name) without separate logic, which is the main advantage over ampersand-based concatenation that produces "John Smith" with double space when the middle is blank.

For combining ranges of columns rather than individual cells, TEXTJOIN accepts ranges as well. =TEXTJOIN(", ", TRUE, A2:E2) joins all five cells in row 2 from A through E with comma separators. This range syntax is more concise than listing each cell individually and works particularly well for variable-width data where the number of columns may grow over time. As columns are added to the range, the formula automatically includes them without modification.

For wider data with many columns, TEXTJOIN scales to substantial complexity. =TEXTJOIN(" - ", TRUE, A2:Z2) joins all 26 cells from A through Z, separating each pair with " - " and skipping blanks. The function handles the looping, separator logic and blank handling internally. Without TEXTJOIN, the equivalent ampersand formula would be impractical to write or read; TEXTJOIN's strength is exactly this kind of multi-column scenario where ampersand concatenation breaks down.

The ampersand operator is the original Excel concatenation method and works in every version. =A2 & " " & B2 & " " & C2 builds the same kind of result as TEXTJOIN but with explicit syntax for each separator. The disadvantage with multiple columns is the formula readability — each separator and value must be explicitly listed, and the formula grows long quickly with 4 or 5 columns. For 3-column combinations the ampersand approach is still readable; beyond that, TEXTJOIN produces dramatically cleaner formulas.

The blank-cell problem with ampersand concatenation is the second reason to prefer TEXTJOIN for multi-column work. Consider a name with first/middle/last where some people have no middle name. The ampersand formula =A2 & " " & B2 & " " & C2 produces "John Smith" (double space) when B2 is blank. Hiding this requires nested IF logic: =A2 & " " & IF(B2="", "", B2 & " ") & C2. The TEXTJOIN equivalent =TEXTJOIN(" ", TRUE, A2, B2, C2) handles the same case automatically with no IF logic.

The CONCAT function (newer than CONCATENATE) and the older CONCATENATE function both join values without separators. =CONCAT(A2, B2, C2) and =CONCATENATE(A2, B2, C2) both produce "JohnQSmith" from three cells. Neither offers the separator or blank-handling that TEXTJOIN provides. CONCAT is preferred over CONCATENATE in modern Excel because it accepts range references like =CONCAT(A2:C2) which CONCATENATE does not. Use CONCAT when you genuinely need an unseparated join; use TEXTJOIN when you need separators.

Flash Fill is a different category — pattern-based static concatenation rather than formula-based dynamic concatenation. Type the desired output for the first row in the destination column. Press Ctrl+E. Excel infers the pattern from your example and fills the rest of the column with values that match. Flash Fill works for many concatenation patterns including name combinations, ZIP code formatting and similar transformations. The result is static text rather than formulas, which means it does not update when source data changes but also does not require formula maintenance.

Power Query is the most powerful tool for combining columns in analytical pipelines. Unlike formulas that live in worksheet cells, Power Query operates as a transformation pipeline that loads data, applies steps and outputs results. The Merge Columns transformation in Power Query takes selected columns and combines them with a chosen separator. The combined column appears in the output; the original columns can be kept or removed depending on your need. Pipeline approach is ideal for repeating data preparation across multiple datasets.

The Power Query path opens with Data > From Table/Range when your data is already in Excel, or Data > Get Data for external sources. The Power Query Editor opens in a separate window. Select the columns to merge by Ctrl+clicking each header. Right-click any selected header and choose Merge Columns. The dialog asks for the separator (Space, Comma, Tab, Custom) and the new column name. After clicking OK, the merged column appears with the original columns removed (the default behavior). Click Close & Load to send the transformed data back to the worksheet.

The Power Query approach has substantial advantages for ongoing data work. The transformation steps are recorded and applied automatically when data refreshes. New rows added to the source data flow through the same merge transformation without manual rework. The pipeline is documented in the Applied Steps panel showing each transformation in order. For users producing the same combined columns from refreshed data each week or month, Power Query is dramatically more maintainable than per-cell formulas.

For users new to Power Query, the learning curve is real but worth investing. The basic Merge Columns operation takes 30 seconds once you know where to click. The broader Power Query toolkit includes column splitting, filtering, sorting, lookup-style joins, conditional logic and dozens of other transformations. Mastering even the basic capabilities pays back across years of analytical work. Most data analysts using Excel daily eventually invest in Power Query proficiency because the time savings on recurring tasks are substantial.

For combining columns with custom separators, TEXTJOIN handles any character or string as the delimiter. =TEXTJOIN(" / ", TRUE, A2:C2) uses " / " as the separator — useful for displaying coded values like "East / Q3 / Active". =TEXTJOIN(CHAR(10), TRUE, A2:C2) uses a line break as the separator, producing multi-line text within a single cell. The CHAR(10) trick combined with cell wrapping (Format Cells > Alignment > Wrap text) produces formatted multi-line output useful for address blocks and similar applications.

For combining columns conditionally based on row data, wrap TEXTJOIN around an IF construction. =TEXTJOIN(" ", TRUE, IF(D2="Active", A2, ""), B2, C2) only includes A2 when D2 equals "Active"; the conditional logic produces empty strings that TEXTJOIN's ignore_empty flag skips. This pattern handles complex conditional concatenation cases that pure TEXTJOIN alone cannot. Array formulas and IF logic combine with TEXTJOIN to handle nearly any conditional combination scenario.

For combining columns across multiple rows (rather than within a single row), TEXTJOIN with a vertical range produces stacked output. =TEXTJOIN(", ", TRUE, A2:A100) combines 99 cells from column A into a single comma-separated string — useful for building summary lists, combining categorical values into a single display. The same pattern handles tag aggregation, member lists and similar scenarios where multiple rows need to collapse into a single combined value.

For very large data combinations, the TEXTJOIN function has a 32,767 character output limit (Excel's maximum cell length). Combining hundreds of long values may exceed this limit and produce a #VALUE! error. For such cases, split the combination into multiple cells or use Power Query for the aggregation. Most everyday concatenation falls well below the limit; very large list aggregations are the typical situation that hits the cap.

The decision tree for choosing a method is concrete. For ongoing dynamic combinations of 3+ columns where the result should update with source data, TEXTJOIN is the right answer. For one-time static results, Flash Fill saves typing the formula. For analytical pipelines that refresh regularly with new data, Power Query is the most maintainable approach. For older Excel versions before Excel 2019, the ampersand operator with nested IF for blank handling is the workaround when TEXTJOIN is not available.

For users frequently combining the same column structure across many spreadsheets, building a reusable pattern saves time. Save a TEXTJOIN formula as a named range or a small VBA function for common name-combination patterns. Or build a Power Query template that handles the merge plus other typical transformations. The investment in a reusable component pays back across the dozens or hundreds of spreadsheets where the same combination logic applies.

For users with bilingual or special-character data, TEXTJOIN handles Unicode correctly across all major languages. Names with accent marks, Chinese characters, Arabic script and other non-ASCII text combine reliably without garbling. The same applies to special characters within names (apostrophes in O'Brien, hyphens in compound surnames, etc.). The function does not interpret content beyond joining the values, so it preserves whatever the source cells contain. The Unicode handling matches Excel's underlying support for international text.

For users moving from older Excel versions to current versions, the upgrade path benefits include TEXTJOIN, CONCAT and Flash Fill. Excel 2007 and 2010 lack TEXTJOIN entirely; combining columns required ampersand concatenation with nested IF logic. Excel 2013 added Flash Fill as the first major usability improvement. Excel 2019 and Excel 365 added TEXTJOIN and CONCAT as proper functions. Users still on older versions should upgrade if regular column combination work is part of their job — the productivity improvement from TEXTJOIN alone justifies the version upgrade for many users.

For users sharing workbooks across mixed Excel versions, compatibility matters. TEXTJOIN works in Excel 2019, Excel 365 and Excel for the web. CONCAT works in Excel 2019, Excel 365 and Excel for the web. CONCATENATE and the ampersand operator work in every version including Excel 2007. If your workbook may open in Excel 2010 or 2007, stick with the ampersand operator with explicit IF logic for blank handling. If everyone using the workbook has Excel 2019 or later, TEXTJOIN is the cleaner approach.

For users in Google Sheets, the function names are similar. TEXTJOIN, CONCATENATE, CONCAT and the ampersand operator all work in Google Sheets with the same syntax as Excel. JOIN is the Google-Sheets-specific function similar to TEXTJOIN; it accepts a delimiter and an array but lacks the ignore_empty flag. Files convert reliably between Excel and Sheets for these functions. The conceptual model is identical across spreadsheet applications; only minor syntax details vary.

For the practical workflow of combining columns in a typical data preparation task, the steps follow a consistent pattern. Identify the source columns. Decide on separator and blank handling. Add a destination column to the right of the source columns. Type the TEXTJOIN formula on the first data row. Drag the formula down to all rows. Verify the results on edge cases (empty rows, rows with blanks in middle columns). Convert to static values if you no longer need the formulas — copy, paste-special-as-values, then delete the source columns if they are no longer needed.

For users worried about formula performance on very large datasets, TEXTJOIN is fast even at scale. A worksheet with 100,000 rows of TEXTJOIN formulas combining 3 columns each recalculates in seconds on modern hardware. Power Query is faster for very large transformations because it operates outside the worksheet recalculation engine, but for everyday business data the worksheet formula approach handles the volume with no noticeable lag. Performance is rarely the deciding factor in choosing between methods.