If you use Excel often—in business, school, or data work—you’ll likely need to merge data from two or more columns. You often need to combine first and last names, merge address fields, or join product IDs with their descriptions. Excel may seem simple, but it has many methods based on your needs. Some are easy, while others pack more power.
This guide covers everything you need to know about combining columns. You’ll learn how to:
- Add spaces or custom text between cells
- Keep date formats intact
- Remove formulas after combining
- Avoid common errors
How to Combine Two Columns in Excel?
Combining two columns in Excel means joining the contents of two separate cells into one. Excel does not have a “combine” button, but it provides multiple functions to achieve this.
Option 1: Using the Ampersand Operator (&)
This is the quickest method to combine two cells.
Formula Example:
=A2 & B2
If A2 contains “John” and B2 contains “Smith”, the result will be:
JohnSmith
Pros:
- Easy to use
- Works in any Excel version
- Great for simple tasks
Cons:
- No automatic spacing between values
- Can look messy without formatting
Option 2: Using the TEXTJOIN Function
TEXTJOIN is a more advanced and powerful option introduced in Excel 2016 and later.
Formula Example:
=TEXTJOIN(“”, TRUE, A2, B2)
Breakdown:
- “” – No delimiter (you can add a space or symbol here)
- TRUE – Ignore empty cells
- A2, B2 – Cells to combine
Pros:
- Cleaner for long lists or more than two columns
- Can ignore blanks automatically
- Accepts ranges like A2:C2
How to Put Additional Text Within Combined Cells?
Sometimes you don’t just want to merge the data—you want to add a label, symbol, or some custom text between values. This helps make your data more human-readable, especially in reports or forms.
Let’s say:
- Column A has a product code like “XY123”
- Column B has a description like “LED Bulb”
You want the output: XY123 – LED Bulb
Here’s the formula:
=A2 & ” – ” & B2
You can insert:
- Hyphens: ” – “
- Slashes: ” / “
- Text strings: ” belongs to “, ” from “, etc.
Real-world example:
=FirstName & ” ” & LastName & ” – Employee”
Output: John Smith – Employee
This is very useful for creating combined identifiers, display names, or reference labels.
How to Put a Space Between Combined Cells?
By default, if you combine two cells using =A2 & B2, Excel won’t include a space. That can be problematic, especially for names, addresses, or sentences.
Solution:
Add a space manually using quotes ” “.
Formula:
=A2 & ” ” & B2
Example:
- A2 = “New”
- B2 = “York”
Result:
New York
You can add:
- One space: ” “
- Two spaces: ” “
- Custom spacing or line breaks (with CHAR(10) for new lines if needed)
One common need when using Excel is handling name fields, locations, or email formats.
How to Combine Columns with Date Format?
If one of the columns contains dates, you might run into an issue. Excel stores dates as numbers internally. So when you combine a date with text, you might see weird results like:
John 44927
Instead, use the TEXT function to convert the date into a human-readable format.
Correct Formula:
=A2 & ” on ” & TEXT(B2, “mm/dd/yyyy”)
Custom date formats:
- “mm/dd/yyyy” → 01/13/2025
- “dd-mmm-yyyy” → 13-May-2025
- “dddd, mmmm dd, yyyy” → Tuesday, May 13, 2025
Example:
- A2 = “Meeting”
- B2 = 13-May-2025
Output:
Meeting on 05/13/2025
This is critical for generating dynamic reports, timestamped descriptions, or daily logs.
How to Get Rid of Formulas in Merged Columns?
When you combine data using formulas, the output is still a live formula. That means if the original cell changes, so will the combined result.
To finalize the combined result as plain text, remove the formula. This is important, especially before sending it to someone else.
Steps to Remove Formulas:
- Select the column with formulas (e.g., C2:C100)
- Press Ctrl + C to copy
- Right-click > Paste Special > Values
Why it’s important:
- Avoids accidental formula breaks
- Makes data portable
- Required for clean exports to CSV or other apps
After this, your cells will contain just the combined text—not a formula.
How to merge data from two different columns in Excel
FAQ’s
Can I combine more than two columns in Excel?
Yes, you can. Just extend the formula:
=A2 & B2 & C2
Or, use TEXTJOIN for cleaner syntax:
=TEXTJOIN(” “, TRUE, A2:C2)
This joins all three cells with a space between each.
Will combining columns delete the original data?
No. Combining columns with a formula does not remove or modify the original data. It simply creates a new column with the merged result.
How do I handle blank cells during merge?
Use TEXTJOIN and set the second argument to TRUE. It automatically ignores empty cells.
=TEXTJOIN(” “, TRUE, A2, B2, C2)
How can I combine data from different sheets?
Reference the cells from each sheet by name:
=Sheet1!A2 & ” ” & Sheet2!B2
Why is my date not displaying correctly when I merge cells?
Excel converts dates to numbers unless you use the TEXT function:
=TEXT(B2, “mm/dd/yyyy”)
Always format dates properly when merging.
Conclusion
Merging columns in Excel is a key skill. It saves time and makes data clearer. No matter if you’re a student, a business analyst, or prepping datasets for SEO or email marketing, these techniques can simplify your workflow.
We explored:
- Basic merging with &
- Advanced merging with TEXTJOIN
- Adding custom text and formatting
- Keeping dates intact
- Making final text static (no formulas)