Answer

To combine data in two columns in Excel, use one of these common methods: the ampersand operator (&), the CONCAT/CONCATENATE functions, TEXTJOIN (when you need a delimiter and to skip blanks), Flash Fill, Power Query, or a short VBA macro. Choose the method that fits your Excel version and the shape of your data.

Method 1 — Use & (simple and universal)

Use & to join two cells with or without a separator.

1. In the cell where you want the combined text, type:

   =A2 & B2

2. To add a space between values, type:

   =A2 & " " & B2

3. Press Enter, then drag the fill handle down to copy the formula.

Method 2 — Use CONCAT or CONCATENATE

Use CONCAT in recent Excel versions, or CONCATENATE in older workbooks.

1. For recent Excel:

   =CONCAT(A2, " ", B2)

2. For older Excel:

   =CONCATENATE(A2, " ", B2)

3. Press Enter and fill down.

Method 3 — Use TEXTJOIN (skip blanks and use a delimiter)

Use TEXTJOIN when you want a delimiter (comma, space) and to ignore empty cells.

1. Example that joins with a space and ignores blanks:

   =TEXTJOIN(" ", TRUE, A2, B2)

2. Press Enter and copy down.

Method 4 — Flash Fill (fast pattern-based fill)

Use Flash Fill when the combined result follows an obvious pattern.

1. In the first result cell next to your data, type the desired combined result (for example John Smith).

2. Select the cells you want filled or put the active cell in the next row and press:

   • Windows: Ctrl + E

   • Mac: Cmd + E (availability depends on Excel version)

3. Excel fills the column based on the pattern you provided.

Method 5 — Power Query (best for large data or repeated transforms)

Power Query merges columns into a new column without formulas and is ideal for repeated ETL tasks.

1. Select your table or data range and choose Data → From Table/Range.

2. In Power Query Editor, select the two columns (use Ctrl+click).

3. Right-click a selected column header → Merge Columns.

4. Choose a separator (space, comma, custom) and a name for the new column.

5. Click Close & Load to return the transformed table to the worksheet.

Method 6 — VBA macro (automate combining into a new column)

Use this when you want a one-click automation.

1. Press Alt+F11 to open the VBA editor (Windows) or Developer → Visual Basic (Mac).

2. Insert a Module and paste:

   Sub CombineTwoColumns()

       Dim ws As Worksheet

       Dim lastRow As Long

       Set ws = ActiveSheet

       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

       Dim i As Long

       For i = 2 To lastRow

           ws.Cells(i, "C").Value = ws.Cells(i, "A").Value & " " & ws.Cells(i, "B").Value

       Next i

   End Sub

3. Run the macro to put combined values in column C.

What if I need to skip empty cells when combining?

To combine two cells but ignore an empty one, use conditional formulas or TEXTJOIN.

1. Using TEXTJOIN:

   =TEXTJOIN(" ", TRUE, A2, B2)

2. Using & with conditional logic:

   =IF(A2="", B2, IF(B2="", A2, A2 & " " & B2))

How to preserve leading zeros or specific number formats when combining?

To keep leading zeros or apply formatting, convert numbers to text with TEXT().

1. Preserve a ZIP code from A2 when A2 is numeric:

   =TEXT(A2, "00000") & " " & B2

2. Format a date in A2 before combining:

   =TEXT(A2, "mm/dd/yyyy") & " - " & B2

How to combine many columns or entire ranges into one cell?

Use TEXTJOIN for many columns or a dynamic range.

1. To join A2:E2 with commas and skip blanks:

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

How to output combined values as static text (remove formulas)

To replace formulas with values:

1. Select the combined column.

2. Press Ctrl + C.

3. Right-click → Paste Special → Values.

What are the important version differences between Windows and Mac?

On modern Excel for both Windows and Mac (Office 365 or Excel 2019+), CONCAT, TEXTJOIN, and Flash Fill are available. Older Excel versions may lack TEXTJOIN and CONCAT; CONCATENATE and & work everywhere. Flash Fill was introduced earlier on Windows; Mac versions gained similar features later. Power Query is fully featured in Windows; Mac support has improved, but can vary by release. Use simple formulas for maximum cross-platform compatibility.

Which method should I choose?

1. Use & or CONCAT for simple, universal needs.

2. Use TEXTJOIN when you need a delimiter and to skip blanks.

3. Use Flash Fill for quick one-off pattern-based combinations.

4. Use Power Query for repeatable, large-scale transformations.

5. Use VBA when you need automation across many sheets or workbooks.

Troubleshooting tips

1. When results look like numbers but appear truncated, check cell formatting and widen the column.

2. When leading zeros disappear, wrap numeric cells with TEXT() and a format mask.

3. When formulas return #NAME?, ensure function names are available in your Excel version and spelled correctly.

4. When Flash Fill does not work, verify the pattern is clear and that Excel’s Flash Fill option is enabled in Options.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report