How to Combine Data from Two Cells in Google Sheets

Combining the first name in one column with the last name in another is a classic spreadsheet task, but it’s often just the beginning. Whether you're creating unique IDs, generating full addresses, or constructing product descriptions, merging data from separate cells is fundamental to organizing your data. This tutorial will walk you through three different methods in Google Sheets for combining cell data, from the quick-and-easy ampersand to more powerful functions that can handle entire data ranges at once.

Why Combine Data from Cells?

While an individual task might be simple, the ability to combine cell data unlocks more powerful ways to organize and present your information. Most raw data isn't ready for reporting or analysis "out of the box." You often need to clean it up and combine it first. Here are a few common scenarios where you might need to merge cells:

• Creating Full Names: Your CRM or contact list probably exports first and last names in separate columns. To create mailing labels or personalized emails, you’ll need a single “Full Name” column.
• Building Full Addresses: Data often comes with street, city, state, and zip code in separate columns. Combining them into one correctly formatted address is essential for shipping, logistics, and mapping.
• Generating Product SKUs or IDs: You might need to generate unique product identifiers by combining a category name with a product number (e.g., "SHIRT" + "1056" becomes "SHIRT-1056").
• Constructing URLs: If you have a list of URL slugs, you might combine your base domain (e.g., "https://www.yourstore.com/products/") with the slug in another cell to create full, clickable links.
• Cleaning Data for Reports: Sometimes you need to combine dimensions to create a more readable label for charts and dashboards, like concatenating a marketing campaign name with its month to create labels like "Summer Sale - July."

Learning how to handle these everyday tasks efficiently will save you countless hours of mind-numbing manual work.

Method 1: Use the Ampersand (&) Operator

The ampersand & is the fastest and most direct way to combine, or concatenate, cells in Google Sheets. Think of it as the plus sign (+) for text. It simply joins the contents of the cells you point to, one after the other.

Let's use the most common example: combining a first name in column A and a last name in column B.

Sample Data:

• Cell A2: Jane
• Cell B2: Doe

Step-by-Step Instructions:

1. Select the destination cell. This is where you want the combined name to appear. Let's use cell C2.
2. Start the formula. Type the equals sign = to begin.
3. Reference the first cell. Click on cell A2 or type A2.
4. Add the ampersand. Type the & symbol.
5. Reference the second cell. Click on cell B2 or type B2.

Your formula should now look like this:

=A2&B2

1. Press Enter.

The result in cell C2 will be "JaneDoe". Notice there's no space between the names. The ampersand joins the values exactly as they are.

To fix this, you need to add a space as its own piece of text in quotes. Modify the formula by inserting " " with ampersands on both sides.

The corrected formula is:

=A2&" "&B2

This tells Google Sheets to: take the value in A2, join it with a space, then join that with the value in B2. Now, cell C2 correctly shows "Jane Doe".

You can use this method to add any text or punctuation. For example, to get "Doe, Jane," you'd use:

=B2&", "&A2

To apply this to your entire column, click on cell C2, then double-click the small blue square (the "fill handle") that appears in the bottom-right corner of the cell. Google Sheets will automatically copy the formula all the way down your dataset.

Method 2: Use the CONCATENATE Function

If you prefer using functions for better readability, CONCATENATE is Google Sheets' dedicated function for this job. It does the exact same thing as the ampersand operator but wraps the logic in a named function, which some users find easier to read and debug.

The syntax is: CONCATENATE(string1, [string2, ...]) where you list each cell or text string you want to join, separated by commas.

Let’s use the same first and last name example.

Step-by-Step Instructions:

1. Select the destination cell (C2).
2. Type the formula name:

=CONCATENATE(

1. Select the first cell (A2). Add a comma.
2. Add the space. Type " ", followed by another comma. Remember to use quotes for any text strings you add.
3. Select the second cell (B2).
4. Close the parenthesis and press Enter. Your full formula should be:

=CONCATENATE(A2, " ", B2)

The result in C2 is "Jane Doe", just like before. Both the ampersand and CONCATENATE get you to the same place. Choosing between them is mostly a matter of personal preference. For simple joins, many people find the ampersand faster. For formulas with many components, CONCATENATE can make the structure clearer.

Method 3: Use the TEXTJOIN Function

While the first two methods are great for combining a handful of cells, they become tedious if you need to combine a long list of cells, especially if you want the same separator (like a comma or space) between each one. This is where TEXTJOIN shines - it’s the most powerful and flexible option here.

TEXTJOIN lets you specify a single separator, or delimiter, to use between all the elements and has a built-in option to ignore empty cells.

The syntax is: TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

• delimiter: The character(s) you want to place between each item (e.g., " ", ", ", "-").
• ignore_empty: Set this to TRUE if you want the function to skip over any empty cells in your range.
• text1, [text2, ...]: The cells or range of cells you want to combine. This is key - you can use a range like A2:D2!

Let’s use an address example. You have Street (A2), City (B2), State (C2), and ZIP (D2).

Step-by-Step Instructions:

1. Select the destination cell (E2).
2. Start the TEXTJOIN function.
3. Specify the delimiter. We want a comma and a space between each part, so we'll use ", ".
4. Choose whether to ignore empty cells. In most cases, TRUE is the best choice to avoid strange double-commas if, for example, an "Apt #" cell is empty. Let’s use TRUE.
5. Select the range of data. Instead of clicking each cell individually, you can just click and drag from A2 to D2 to get the range A2:D2.
6. Close the parenthesis. The completed formula looks like this:

=TEXTJOIN(", ", TRUE, A2:D2)

Assuming A2 is "123 Main St", B2 is "Anytown", C2 is "CA", and D2 is "90210", this one formula produces the perfectly formatted result: "123 Main St, Anytown, CA, 90210". Imagine doing that with the ampersand - it would be a mess of ampersands and quotes: =A2&", "&B2&", "&C2&", "&D2. TEXTJOIN is much cleaner and scalable.

Bonus Tips & Common Issues

When you combine cells, you can run into a few tricky spots. Here’s how to handle them.

1. Combining Text and Numbers (or Dates)

If you try to join text with a cell containing a formatted number, currency, or date, Google Sheets converts that value into a plain number. For example, if A2 is "Your total is:" and B2 has a value of $50.35, combining them with =A2&" "&B2 will give you "Your total is: 50.35" — the currency formatting is lost.

To fix this, use the TEXT function to format the number or date before you join it.

=A2&" "&TEXT(B2, "$#,##0.00")

For dates, if B2 contains 12/25/2024, you can format it like this:

="Holiday is on: "&TEXT(B2, "mmmm d, yyyy") Result: Holiday is on: December 25, 2024

2. Replacing Formulas with Static Values

Your new combined cell contains a formula. If you delete the original columns (like the separate First Name and Last Name columns), your formulas will break and show an error. To prevent this, you should convert your formulas into static values.

• Select the entire column with your combined data.
• Copy it (Ctrl+C or Cmd+C).
• With the column still selected, right-click and choose Paste special > Values only (or use the shortcut Ctrl+Shift+V or Cmd+Shift+V).

This replaces the underlying formulas with their text results. Now you can safely delete the original columns.

Final Thoughts

Mastering these text combination methods moves you one step away from manual data entry and one step closer to efficient spreadsheet management. For quick, one-off jobs, the ampersand (&) is your best friend. For more structured formulas, CONCATENATE offers clarity. And when you need to merge entire ranges with a consistent separator, TEXTJOIN is the most powerful tool for the job.

Manually preparing spreadsheet data is often the most time-consuming part of analytics, forcing you to combine columns, clean formats, and wrangle data before you can even begin to build a report. At Graphed, we automate all that tedious work. You connect your data sources — like Google Analytics, Shopify, or Salesforce — just once. From there, our AI data analyst understands the underlying structure, so you can just ask in plain English for the report you need. Instead of building formulas to combine first and last names, you can just ask, "Show me sales by team member this quarter," and get an instant, real-time dashboard without touching a single spreadsheet.