How to Combine Two Columns in Excel: A Complete Step-by-Step Guide

How to Use Flash Fill to "Merge Cells" from Columns in Excel

1. Create a target column: Ensure you have an empty column next to the data you want to combine.

2. Type the first entry: In the first empty cell (e.g., C2), type exactly how you want the combined data to look using the data from columns A and B.

3. Provide a second example (Optional): If Excel doesn't catch on immediately, type the desired result in the second cell (C3).

   4. Trigger the Magic: Press Ctrl + E on your keyboard. Alternatively, go to the Data tab on the ribbon and click the Flash Fill button.

Professional Tip: Flash Fill is "static." If you change the name in Column A later, the combined result in Column C will not update automatically. For dynamic data, use the formula methods listed below.

The Basic Logic

The formula structure follows a simple pattern:

=Cell1 &"Separator" & Cell2

=Cell1 &"Separator" & Cell2

`=Cell1 & "Separator" & Cell2`

Steps to Implement

1. Select the cell where you want the combined data to appear.
2. Enter the formula. For example, to join a First Name (A2) and Last Name (B2) with a space:

=A2&" "& B2

=A2&" "& B2

1. Press Enter.

2. Double-click the Fill Handle (the small green square at the bottom-right of the cell) to copy the formula down the entire column.

CONCAT vs. CONCATENATE

• CONCATENATE: The legacy function. It requires you to select every cell individually.
• CONCAT: The modern replacement. It allows you to select entire ranges (e.g., $A2:D2$) to merge them all at once.

Steps to Implement CONCAT

1. Click on your target cell.
2. Type the following formula to combine cells with a comma separator:

=CONCAT(A2,",",B2)

=CONCAT(A2,",",B2)

=CONCATENATE(A2, ",", B2)

1. If you have a long list of cells to merge without separators, you can simply use:

=CONCAT(A2:Z2)

=CONCAT(A2:Z2)

The provided code appears to be an Excel formula rather than C# code. If you intended to provide C# code for conversion to VB.NET, please provide the correct C# code snippet.

This will merge multiple cells with ease, as seen here:

Why TEXTJOIN is Superior

With standard concatenation, if a middle name is missing, you often end up with two spaces in the middle of your result. TEXTJOIN allows you to "ignore blank cells."

Steps to Implement

1. Select your target cell.

   2. Enter the formula:

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

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

   `=TEXTJOIN(" ", TRUE, A2, B2)` is a formula used in Excel, not C# code. If you intended to convert a C# code snippet that involves string joining, please provide the correct C# code. If this is indeed what you want to convert, please clarify how you would like it to be represented in VB.NET or any other context.

   $vbLabelText   $csharpLabel

   • " ": This is the delimiter (the space between words).

   • TRUE: This tells Excel to ignore any empty cells in the range.

   • A2, B2: These are the cells you are merging.

This will result in your two columns of data being combined into a single column like this:

Steps to Merge Columns in Power Query

1. Select your data range and go to Data > From Table/Range.

2. In the Power Query Editor window, hold Ctrl and click the headers of the columns you want to combine.

3. Right-click on one of the selected headers and choose Merge Columns.

4. Choose your Separator (Space, Tab, Comma, etc.) and give the new column a name.

5. Click OK, then click Close & Load to return the merged data to Excel.

1. Merging Dates and Currency

If you try to combine a name with a date using =A2 & B2, the date will appear as a five-digit number (e.g., 45120). This is because Excel stores dates as serial numbers.

• Solution: Use the TEXT function to keep the formatting:

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

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

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

  $vbLabelText   $csharpLabel

2. Excessive Spaces

If your source data has accidental leading or trailing spaces, your merged results will look messy.

• Solution: Wrap your cell references in the TRIM function:

  =TRIM(A2) &"  "& TRIM(B2)

  =TRIM(A2) &"  "& TRIM(B2)

  `=TRIM(A2) & "  " & TRIM(B2)`

  $vbLabelText   $csharpLabel

3. Numbers Turning into Text

Once you merge two columns, the result is always a String (text). You cannot perform mathematical sums on a cell that contains "Item 100".

• Solution: If you are merging two numbers to create a new ID, and you need it to stay a number, wrap the whole formula in VALUE():

  =VALUE(A2 & B2)

  =VALUE(A2 & B2)

  `=VALUE(A2 & B2)` is an Excel formula, not C# code. If you intended to convert a C# expression involving string concatenation and conversion to a number, please provide the C# code. If this is indeed an Excel formula, it doesn't require conversion to VB.NET.

  $vbLabelText   $csharpLabel

Why Use IronXL?

• Speed: Process massive workbooks faster than the Excel GUI or Interop.
• No Dependencies: Works on Linux, Azure, and Windows without requiring Excel to be installed.
• Accuracy: Eliminate the risk of a user accidentally breaking a formula in a shared workbook.

Code Snippet: Merging Columns in C#

using IronXL;
// Load the existing workbook
WorkBook workbook = WorkBook.Load("Customer_Data.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Iterate through the rows to combine Column A (First Name) and B (Last Name)
for (int i = 1; i <= sheet.RowCount; i++)
{
    string firstName = sheet["A" + i].StringValue;
    string lastName = sheet["B" + i].StringValue;
    // Set the value of Column C to the combined string
    sheet["C" + i].Value = $"{firstName} {lastName}";
}
// Save the updated file
workbook.SaveAs("Consolidated_Customer_Data.xlsx");

using IronXL;
// Load the existing workbook
WorkBook workbook = WorkBook.Load("Customer_Data.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Iterate through the rows to combine Column A (First Name) and B (Last Name)
for (int i = 1; i <= sheet.RowCount; i++)
{
    string firstName = sheet["A" + i].StringValue;
    string lastName = sheet["B" + i].StringValue;
    // Set the value of Column C to the combined string
    sheet["C" + i].Value = $"{firstName} {lastName}";
}
// Save the updated file
workbook.SaveAs("Consolidated_Customer_Data.xlsx");

Imports IronXL

' Load the existing workbook
Dim workbook As WorkBook = WorkBook.Load("Customer_Data.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

' Iterate through the rows to combine Column A (First Name) and B (Last Name)
For i As Integer = 1 To sheet.RowCount
    Dim firstName As String = sheet("A" & i).StringValue
    Dim lastName As String = sheet("B" & i).StringValue
    ' Set the value of Column C to the combined string
    sheet("C" & i).Value = $"{firstName} {lastName}"
Next

' Save the updated file
workbook.SaveAs("Consolidated_Customer_Data.xlsx")