Excel Opposite of Text to Columns: How to Combine Data

How-to basics · Updated November 21, 2026

The opposite of Excel’s Text to Columns command is combining data from separate columns into one cell. Use a formula such as =A2&" "&B2 to join two cells with a space, or use Flash Fill or Power Query when you want a different workflow; Excel does not have a single command named “Columns to Text.”

What “opposite of Text to Columns” means

Text to Columns takes one column of text and splits it into multiple columns, usually by a delimiter such as a comma, space, or tab, or by fixed character widths. Its reverse is often called combining, joining, concatenating, or merging columns: taking values in separate cells and assembling them into one text value.

For example, if A2 contains “Maya” and B2 contains “Chen,” joining them with a space produces “Maya Chen.” If A2 contains “North,” B2 contains “Depot,” and C2 contains “17,” a combined result might be “North Depot 17.” The exact delimiter and order depend on what the combined text needs to represent.

This is different from Excel’s Merge & Center command. Merging cells changes the worksheet layout and can discard the contents of all but the upper-left cell. It does not combine the text from the cells. To retain every value, create a combined result with a formula, Flash Fill, or Power Query.

Choose the right way to combine columns

For a small, ongoing worksheet, a formula is usually the clearest choice: it updates when the source cells change. Flash Fill is handy for a quick one-time transformation when Excel can recognize a pattern. Power Query is useful for repeatable imports or larger data-cleaning tasks that you may refresh later.

  • Formula: best when the result should update with the source data or when the joining rules must be explicit.
  • Flash Fill: best for quickly creating a pattern-based result without writing a formula.
  • Power Query: best for a repeatable process that combines columns as part of importing and transforming data.
  • Copy and paste values: useful after a formula has produced the desired output and you need fixed text rather than a live calculation.

The instructions below are for Excel for Microsoft 365 on Windows. Excel for Mac has the same general formula options, though ribbon placement and shortcuts can differ. Excel for the web supports formulas, but some desktop tools and their controls may not be available or may differ.

Join cells with the ampersand formula

The ampersand (&) is a direct way to join cell contents and literal text. It works well for two or a few fields and has been supported across Excel versions. Type the formula in the destination cell, then fill it down for the remaining rows using the fill handle or by copying the formula.

With first names in A2 and last names in B2, where A2 is Maya and B2 is Chen, enter =A2&" "&B2. The result is Maya Chen. The quoted space is the separator; replace it with a comma, hyphen, or other text as needed. For a comma and space, for example, the middle part would be ", ".

With a street name in A2, a street type in B2, and a unit number in C2, an expression can join those cells with chosen separators. With A2 containing “8 Oak,” B2 containing “Road,” and C2 containing “Unit 4,” the formula =A2&" "&B2&", "&C2 returns 8 Oak Road, Unit 4. This method gives precise control, but the formula gets longer as you add more columns.

Use TEXTJOIN when there are several fields

TEXTJOIN is convenient when you want one separator between many cells. Its first argument is the delimiter, its second specifies whether to ignore empty cells, and the remaining argument or arguments identify the text to join. TEXTJOIN is available in Excel for Microsoft 365 and Excel 2019 or later.

With the values North in A2, a blank in B2, and Depot in C2, enter =TEXTJOIN(" - ",TRUE,A2:C2). The result is North – Depot: the blank cell is skipped, so the formula does not create an extra separator around it. Change TRUE to FALSE if empty cells should be treated as empty entries in the joined sequence; that can leave repeated delimiters where blanks occur.

TEXTJOIN can also combine a range with a consistent separator, such as a space, comma, or line break. For readability, use a delimiter that makes the finished text unambiguous. Joining product codes with no separator, for example, could make adjacent values difficult to distinguish. If each row has different joining rules, the ampersand formula may be easier to understand than a single complex expression.

Use Flash Fill for a pattern-based result

Flash Fill can infer a pattern from examples you type. Suppose A2 contains “Maya” and B2 contains “Chen.” Type “Maya Chen” in C2, then enter a second example in C3 if Excel needs more evidence. Select the next cell in the output column and choose Data > Flash Fill, or press Ctrl+E on Windows. Excel attempts to complete the column using the pattern.

Flash Fill is useful for one-off formatting, such as combining a first and last name, adding punctuation, or rearranging parts of a text value. Check the results before relying on them: it is pattern recognition, not a live formula. If the original names change, the filled results do not automatically recalculate. On Mac, use the Data tab’s Flash Fill command if it is available in your Excel version; keyboard shortcuts can differ. If Flash Fill is unavailable or does not detect the pattern, use a formula instead.

Combine columns in Power Query

Power Query is a better fit when you regularly import or clean data and want the combination step saved in a refreshable process. In Excel for Microsoft 365 on Windows, select a cell in the source data and choose Data > From Table/Range. If the data is not already an Excel table, Excel prompts you to create one. In the Power Query Editor, select the columns to combine, then choose Transform > Merge Columns.

In the merge dialog, choose a separator and provide a name for the new column. Confirm the operation, then choose Home > Close & Load to return the transformed data to Excel. The query keeps the transformation steps, so after updating the source you can refresh the query rather than repeat each manual operation.

Power Query is not the same as a worksheet formula: the output is produced when the query runs or refreshes, rather than recalculating immediately after each cell edit. Its interface and feature availability can vary by Excel version and platform. On Mac, check the Data tab for the Power Query import and editing features supported by that release; if the merge command is not available, a worksheet formula is a practical alternative.

Handle blanks, dates, numbers, and spaces

Joining values converts them into text, so formatting deserves attention. A date cell may display as “Nov 3, 2026” in the worksheet but concatenate as its underlying serial number. To control the displayed date in a joined result, use TEXT with a format code.

With a date in A2 and the words “Ship date:” in B2, where A2 contains November 3, 2026, enter =B2&" "&TEXT(A2,"mmm d, yyyy"). The result is Ship date: Nov 3, 2026. Change the format code inside TEXT to choose another date appearance. The same principle applies to numbers when you need particular decimal places or leading zeros: specify the intended display format rather than assuming concatenation will preserve the cell’s visual formatting.

Spaces are another frequent source of messy results. A cell may contain extra spaces at the start or end, so joining it with another value can create an unexpectedly wide gap. Clean the source data or use TRIM around an individual cell when appropriate. With names in A2 and B2, the formula =TRIM(A2)&" "&TRIM(B2) removes extra spaces at the edges of each name before joining them. TRIM also reduces repeated internal spaces to single spaces, so do not use it if repeated spaces are meaningful.

Common problems and how to avoid them

  • The formula displays as text: check that the cell is not formatted as Text, and that the entry starts with an equals sign. Change the cell format to General and re-enter the formula if needed.
  • There is no separator: Excel joins exactly what the formula specifies. Add a quoted space, comma, or other delimiter between cell references.
  • Blanks leave awkward punctuation: use TEXTJOIN with its ignore-empty option, or add conditional logic when each field needs a different separator.
  • Dates or numbers look wrong: the formula may be using the stored value rather than the displayed format. Use TEXT with a suitable number format when building the combined text.
  • The result does not change after editing the source: this is expected for Flash Fill and pasted values. Use a formula for a live result, or refresh a Power Query output.
  • Combining seems to erase source data: do not use Merge & Center as a substitute for joining text. Keep the source columns and place the combined result in a separate column.

When the formula result is final, you can copy the output column and use Home > Paste > Paste Values to replace formulas with their current results. Keep a copy of the original columns first if you may need to revise the joining rule later.

Frequently Asked Questions

Does Excel have a reverse Text to Columns button?
No. Excel does not provide one universal reverse command with that name. Use a joining formula, Flash Fill, or Power Query to combine separate columns, depending on whether the result needs to update or be repeatable.

What is the simplest formula to combine two columns?
For values in A2 and B2 with a space between them, use =A2&" "&B2. Replace the quoted space with the separator you want. Fill the formula down to apply it to other rows.

How do I combine columns but skip blank cells?
Use TEXTJOIN with TRUE as its ignore-empty argument. For example, with the cells to combine in A2:C2, =TEXTJOIN(" ",TRUE,A2:C2) joins the nonblank values with spaces. TEXTJOIN is available in Excel for Microsoft 365 and Excel 2019 or later.

Can I combine columns and keep the original data?
Yes. Put the formula or transformed result in a new column and leave the source columns untouched. A formula references the original cells; Power Query normally loads its output separately from the source table.

Why does combining columns show a number instead of a date?
Excel stores dates as numbers and may use that stored value when building text. Wrap the date reference in TEXT with a format code, such as TEXT(A2,"mmm d, yyyy"), to specify how it should appear in the combined result.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.