6 Ways to Combine Two Columns with a Space in Excel (Including Modern Functions and Troubleshooting)
The simplest ways to combine two columns with a space in Excel (Excel combine columns with space) are the & operator and the TEXTJOIN function. This guide covers a 10-second fix → how it works → practical examples → troubleshooting all in one place. By the end, you will be able to handle ignoring blank cells, combining multiple columns, and preserving number and date formats with ease.
Quick Fix: Combine Cells as “A B” in 10 Seconds
- Click the cell where you want the result, such as C2.
- Enter one of the following formulas and press Enter.
=A2 & " " & B2Or, to ignore blank cells:
=TEXTJOIN(" ", TRUE, A2, B2) - Double-click the fill handle to fill down automatically.
Tip: Wrap the formula in TRIM(A2 & " " & B2) to automatically remove extra leading and trailing spaces.
Why Does This Happen? (Concepts and Principles)
- &: Fast and widely compatible, but does not ignore blank cells.
- CONCAT: Combines a range, but you need to add delimiters yourself.
- TEXTJOIN: Controls both the delimiter and ignoring blank cells at once, making it ideal for combining multiple columns.
For a detailed comparison:
- Excel TEXTJOIN Guide – Delimiters and Ignoring Blank Cells
- Excel CONCAT vs. TEXTJOIN: Differences and How to Choose
Practical Examples
Sample Data
| A (First Name) | B (Last Name) |
|---|---|
| John | Smith |
| Maria | |
| Kim | Minsoo |
| Park |
1) Basic Two-Column Combination — Super Simple
=A2 & " " & B2To prevent leftover spaces, use:
=TRIM(A2 & " " & B2)2) Ignore Blank Cells — Clean Results
=TEXTJOIN(" ", TRUE, A2, B2)3) Combine Multiple Columns (A:D) at Once
=TEXTJOIN(" ", TRUE, A2:D2)4) Preserve Number and Date Formats
=TEXTJOIN(" ", TRUE, A2, TEXT(B2, "#,##0.00"))=TEXTJOIN(" ", TRUE, A2, TEXT(B2, "yyyy-mm-dd"))=TEXTJOIN(" ", TRUE, A2, TEXT(B2, "00000"))5) Process All Rows at Once with Dynamic Arrays (Excel 365)
=A2:A5 & " " & B2:B5=MAP(A2:A5,B2:B5,LAMBDA(x,y,TRIM(x & " " & y)))=BYROW(A2:B5, LAMBDA(r, TEXTJOIN(" ", TRUE, r)))For more details on cleaning input text, see: Remove Hidden Spaces with Excel TRIM and CLEAN
Alternative Methods (No Formulas)
A) Flash Fill
- Enter one sample in C2 in the format John Smith.
- Select Data > Flash Fill or press Ctrl+E.
Automatically Fill Patterns with Excel Flash Fill
B) Merge Columns with Power Query
- Convert the range to a table (Insert > Table).
- Select Data > From Table/Range to open Power Query.
- Select the two columns → Merge Columns → set the delimiter to Space → Close & Load.
Merge Columns with Power Query (Including Delimiters)
Checklist & Notes
- To ignore blank cells, use
TEXTJOIN(" ", TRUE, ...). - Clean up extra spaces with
TRIM. - Replace NBSP (CHAR(160)) with
SUBSTITUTE(,CHAR(160)," "). - Specify number and date formats with
TEXT. - Automate recurring tasks with Flash Fill or Power Query.
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Double spaces | Combining blank cells | TEXTJOIN(" ", TRUE, ...) or TRIM(...) |
| Leading/trailing spaces | Extra spaces in the source data | Clean up with TRIM |
| Formatting is lost | Text concatenation | Use TEXT(cell,"format") |
| NBSP is not removed | Contains CHAR(160) | TRIM(SUBSTITUTE(cell,CHAR(160)," ")) |
| #NAME? | TEXTJOIN is not supported in older versions | Use TRIM(A2 & " " & B2) instead |
Conclusion & Related Articles
- Complete Excel TEXTJOIN Guide
- CONCAT vs. TEXTJOIN: Which Should You Use and When?
- Remove Hidden Spaces with TRIM and CLEAN
Further learning (official external documentation): Microsoft: TEXTJOIN · Microsoft: CONCAT