How to Combine Columns in Google Sheets: Step-by-Step Guide | SheetXAI

The Problem

You have data in separate columns (like First Name in column A and Last Name in column B) and need to combine them into a single column (Full Name). Or you need to merge multiple columns of data into one.

The Easy Way: Use SheetXAI

If you don't want to manually combine columns, the fastest way is to simply ask SheetXAI.

With SheetXAI, you can open the sidebar and type:

Combine columns A and B into column C, separated by a space.

SheetXAI will instantly combine the columns for you, handling all the formatting and separators automatically. You can combine any number of columns with any separator you want.

The Manual Way: Step-by-Step Instructions

There are several ways to combine columns in Google Sheets. Here are the most common methods:

Method 1: Using CONCATENATE Function

1. Click on the destination cell (e.g., C2)
2. Type the formula: =CONCATENATE(A2," ",B2)
   • A2: First column
   • " ": Separator (space)
   • B2: Second column
3. Press Enter
4. Drag the formula down: Use the fill handle to copy the formula to other rows

Example:

• A2: "John"
• B2: "Doe"
• Formula: =CONCATENATE(A2," ",B2)
• Result: "John Doe"

Method 2: Using CONCAT Function

1. Click on the destination cell (e.g., C2)
2. Type the formula: =CONCAT(A2," ",B2)
3. Press Enter
4. Drag the formula down

Note: CONCAT is simpler than CONCATENATE and works the same way.

Method 3: Using TEXTJOIN Function

1. Click on the destination cell (e.g., C2)
2. Type the formula: =TEXTJOIN(" ",TRUE,A2,B2)
   • " ": Separator (space)
   • TRUE: Ignore empty cells
   • A2, B2: Columns to combine
3. Press Enter
4. Drag the formula down

Advantage: TEXTJOIN can handle empty cells and multiple columns easily.

Method 4: Using the Ampersand (&) Operator

1. Click on the destination cell (e.g., C2)
2. Type the formula: =A2&" "&B2
   • & connects values
   • " " adds a space separator
3. Press Enter
4. Drag the formula down

Example:

• A2: "John"
• B2: "Doe"
• Formula: =A2&" "&B2
• Result: "John Doe"

Combining Multiple Columns

To combine more than two columns:

Using CONCATENATE:

=CONCATENATE(A2," ",B2," ",C2)

Using CONCAT:

=CONCAT(A2," ",B2," ",C2)

Using TEXTJOIN:

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

Using Ampersand:

=A2&" "&B2&" "&C2

Different Separators

You can use any separator between columns:

• Space: " " (single space)
• Comma: ", " (comma and space)
• Dash: "-"
• No separator: "" (empty string)
• Custom: Any text you want

Examples:

• =A2&", "&B2 → "John, Doe"
• =A2&"-"&B2 → "John-Doe"
• =A2&B2 → "JohnDoe" (no separator)

Converting Formulas to Values

After combining columns, you may want to convert the formulas to static values:

1. Select the combined column (e.g., column C)
2. Copy (Ctrl+C / Cmd+C)
3. Right-click → Paste special → Paste values only
4. Or use Edit → Paste special → Paste values only

This removes the formulas and leaves only the combined text.

Common Mistakes to Avoid

1. Forgetting separators: Always include a separator (space, comma, etc.) unless you want values joined directly
2. Not dragging the formula: Remember to copy the formula to all rows
3. Including empty cells: Use TEXTJOIN with TRUE to ignore empty cells
4. Not converting to values: If you delete original columns, formulas will break - convert to values first
5. Wrong cell references: Make sure references match your data structure

Tips for Better Column Combining

• Use TEXTJOIN for flexibility: It handles empty cells and multiple columns better
• Add separators thoughtfully: Consider readability (spaces, commas, dashes)
• Convert to values: After combining, convert formulas to values if you'll delete original columns
• Use SheetXAI for complex combinations: For multiple columns or special formatting, SheetXAI can do it instantly
• Check for extra spaces: Trim extra spaces if needed using TRIM function

Combining Columns with Formatting

To combine columns with formatting (like dates or numbers):

1. Format the result: Use TEXT function to format numbers/dates
2. Example: =TEXT(A2,"mm/dd/yyyy")&" - "&B2
3. This formats the date before combining

Handling Empty Cells

To skip empty cells when combining:

Using TEXTJOIN:

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

The TRUE parameter tells Google Sheets to ignore empty cells.

Using IF statements:

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

Conclusion

Now you know multiple ways to combine columns in Google Sheets. The ampersand (&) operator is quickest for simple cases, while TEXTJOIN is best for complex combinations with multiple columns.

But for those times when you need to combine many columns or apply special formatting, SheetXAI can combine columns automatically with simple commands, saving you time and ensuring consistent results.

Related Guides

• How to Combine Columns in Excel - Excel version
• CONCATENATE in Google Sheets - Combine text strings
• How to Separate Names in Google Sheets - Split combined data
• Google Sheets AI Guide - Learn how AI can automate your Google Sheets workflows
• AI Spreadsheet Tools - Discover how AI transforms spreadsheet work