There are a wide variety of functions and methods in Excel to combine columns. Whether you’re merging first and last names, joining addresses, or creating unique identifiers.
But in real-world scenarios, sometimes things don’t go as smoothly as planned. You might run into weird errors, formatting issues, or even lose essential data if you’re not careful.
In this post, I’ll walk you through the most common problems people face when combining columns in Excel—and how to fix them quickly.
Let’s dive in!
1. #VALUE! Error When Combining Columns
The Problem:
You are getting VALUE! error instead of combined result when using formula like =C2&D2 or =CONCATENATE(C2,D2)
The Fix:
This error occurs when one of the cells contains an error, like #N/A, #VALUE, or #DIV/0!. Excel can’t concatenate such error values.
Solution 1: Try to fix the error in Source Data.
Solution 2: Handle errors when combining cells using the IFERROR() function.
Example:
=IFERROR(C2,"") & IFERROR(D2,"")This will ignore any error and replace it with a blank space.
2. Missing Spaces Between Combined Words
The Problem:
When you combine a first name and a last name like this:
=C2&D2and get a result like ElizabethSmith instead of Elizabeth Smith.
The Fix:
You need to add a space manually(i.e. ” “) in your formula syntax as below:
=C2& " " &D2
3. Dates or Numbers Are Showing Up Weird
The Problem:
When you combine a name with a date or number, you see results like this:
Elizabeth45658 (instead of Elizabeth 01-01-2025).
Excel sometimes converts dates/numbers into their underlying serial numbers.
The Fix:
To fix this issue, you can wrap the number or date in a TEXT function to control the formatting.
Example Formula:
=C2 & " " & TEXT(D2,"mm/dd/yyyy")This will display the date correctly.
For numbers:
=C2 & " " & TEXT(D2,"#,##0.00")It will add commas and decimal points as needed.
4. Blank Cells Create Weird Gaps or Results
The Problem:
Some rows have blank cells, and your combined data either:
- Has extra spaces
- Looks incomplete
- Shows odd formatting
Example:
In the image, you can see there is a leading space in the E3 cell as C3 is blank and we used =C3& ” ” & D3 to combine cell data.
The Fix:
You can handle blank values using the IF or IFERROR function.
Example Formula Without Extra Spaces:
=IF(C3<>"", " " & C3, "") & D3The above formula will only add the space and value from column B if B2 isn’t blank.
Look at the image given below.
5. Combining Columns with Line Breaks Isn’t Working
The Problem:
Sometimes, you want to combine columns with line breaks but you are getting everything on the same line.
The Fix:
Use CHAR(10) to insert a line break. Also don’t forget to turn on Wrap Text in the cell formatting!
Example Formula:
=C2 & CHAR(10) & D2Select the cell → Go to the Home tab → Click Wrap Text.
6. Data Is Overwritten or Lost When Merging Columns Directly
The Problem:
You try to merge cells (using the Merge & Center button), and Excel warns you it will keep only the upper-left value.
The Fix:
Avoid using Merge Cells if you want to combine data. Instead:
- Use a helper column to combine the values (=A2 & ” ” & B2).
- Then copy and paste values if needed.
Pro Tips to Avoid Errors:
- Always keep your original data intact. Work in a separate helper column.
- Use TEXT functions for numbers, dates, and currency.
- Check for hidden spaces or non-printing characters. Use TRIM() or CLEAN() if you’re getting unexpected results.
Bonus Resource: Free Excel File to Practice Fixing Errors
Ready to put what you learned into action?
Download my Excel workbook with all the common combining column errors (and fixes) included!
Get the Excel file here.
This hands-on practice will help you master combining columns without losing data or triggering errors.
Wrapping Up
Combining columns in Excel can be straightforward, but these common errors trip people up all the time. Hopefully, this post helps you troubleshoot and clean up your data like a pro!
Let me know if you face any other errors when combining columns in Excel by commenting below. I am happy to help you!