Combining two columns means joining text or values from one column with text or values from another column. You might combine first name and last name into a full name. You might combine street address, city, and state into a complete address. You might combine product code, product name, and product version into a complete product identifier. Excel provides multiple methods to concatenate columns, each useful in different situations.
The fundamental operation is combining strings (text). If A2 contains “John” and B2 contains “Smith”, you want to combine them into “John Smith” in column C. The combining happens in a formula that joins the values with an optional separator (in this case, a space). Excel treats this operation as string concatenation, joining text values together.
The most important concept: concatenation creates a new value that combines source values. If you later change the source (John becomes Jonathan), the combined value does not automatically update. This is different from formulas that calculate based on other cells. Concatenation is a one-time joining of values. Understanding this distinction prevents confusion.
The Ampersand Operator: Simplest Method
The ampersand (&) operator joins text values together. In cell C2, write =A2&B2. If A2 contains “John” and B2 contains “Smith”, the result is “JohnSmith” (no space). To add a space between, write =A2&” “&B2. Now the result is “John Smith”. The formula uses ampersands to join three things: the first name, a space character (in quotes), and the last name.
The ampersand method is simple and intuitive once you understand it. You are literally concatenating parts together. Add more parts as needed: =A2&” “&B2&”, “&C2 combines first name, space, last name, comma-space, and city. The result is “John Smith, Boston”. Each ampersand joins another part.
Copy the formula down the column to apply it to all rows. Each row combines its corresponding cells. Row 3 combines A3 and B3, row 4 combines A4 and B4, and so on. The relative references automatically adjust as you copy.
The CONCATENATE Function: Traditional Method
CONCATENATE is Excel’s built-in function for joining text. Write =CONCATENATE(A2,” “,B2) to join first name, space, and last name. The result is identical to using ampersands: “John Smith”. CONCATENATE works with any number of arguments separated by commas.
CONCATENATE syntax is CONCATENATE(text1, text2, text3, …). You list each part you want to join as a separate argument.
CONCATENATE is verbose (longer to type) but very clear about what is happening. Some users prefer CONCATENATE because it is obvious you are concatenating. Others prefer ampersand because it is shorter.
In modern Excel, TEXTJOIN is preferred over CONCATENATE because it handles empty cells better and allows separators. But CONCATENATE still works and appears in many existing spreadsheets.
The TEXTJOIN Function: Modern Method
TEXTJOIN is the modern replacement for CONCATENATE, introduced in recent Excel versions. TEXTJOIN allows you to specify a separator that applies between all joined values, making complex concatenations much simpler.
TEXTJOIN syntax is TEXTJOIN(separator, ignore_empty, text1, text2, …). The separator (like a space or comma) appears between each text value. ignore_empty is TRUE or FALSE. If TRUE, empty cells are skipped. If FALSE, empty cells are included as blank space.
Write =TEXTJOIN(” “, TRUE, A2, B2) to join A2 and B2 with a space separator. The ignore_empty parameter handles cases where a cell is empty. With TRUE, if A2 is empty, the separator is skipped and you just get B2’s value. With FALSE, empty cells still appear, potentially creating double spaces or irregular formatting.
TEXTJOIN shines when joining many columns. =TEXTJOIN(“, “, TRUE, A2:E2) joins all values in A2 through E2 with commaspace as separator, skipping empty cells. This is much simpler than writing separate ampersand operators for each combination.
If your Excel version does not support TEXTJOIN, use CONCATENATE with manual separators or ampersand operators.
TEXTJOIN is available in Excel 2016 and later versions.
Adding Separators and Formatting
A separator is the character or string between joined values. Space, comma, comma-space, dash, underscore, or any text can be a separator. Write =A2&”, “&B2&”, “&C2 to join three values with comma-space as separator. Write =A2&”-“&B2 to join with dash as separator. Write =A2&” | “&B2 to join with pipe as separator.
The choice of separator depends on your use case. Addresses typically use comma-space between parts. Names often use space between first and last. Product codes might use underscore or dash to create unique identifiers. Think about how the combined value will be read and used, then choose an appropriate separator.
Line breaks can be separators in modern Excel. Write =TEXTJOIN(CHAR(10), TRUE, A2:E2) to join values with line breaks between them. This is useful for creating multi-line cells where each part appears on a separate line. This is advanced but powerful for specific formatting needs.
Combining with Conditional Logic
You can combine IF with concatenation to conditionally join values. For example, =IF(B2=””, A2, A2&” “&B2) joins A2 and B2 with space only if B2 contains a value. If B2 is empty, just show A2. This prevents irregular spacing from empty cells.
Or =A2&IF(B2<>””, ” (“&B2&”)”, “”) adds B2 in parentheses only if B2 is not empty. The result is “John (Manager)” if B2 contains “Manager”, or just “John” if B2 is empty. This creates professional output that handles missing data gracefully.
These conditional combinations are more sophisticated but follow the same principles: combine concatenation with logic to handle various data scenarios.
Combining Numeric and Text Values
Combining columns works when combining text or when combining numbers as text. If A2 contains 100 (a number) and you write =A2&” units”, the result is “100 units”. Excel converts the number to text for concatenation purposes. The result is text, not numeric.
This is usually not a problem, but be aware that =”100″ (text) and =100 (number) behave differently in calculations. Once you concatenate a number into text, calculations on that combined value require converting back to numeric form.
If you need to concatenate and maintain numeric values, concatenate after calculations are complete. Calculate with numbers, then concatenate the results for display.
Managing Combined Values: The Irreversibility Issue
Important caveat: concatenation creates new values from source values. If you combine first name and last name into full name, the source columns still contain separate first and last names. If you later change “John” to “Jonathan”, the combined column still shows “John Smith” unless the formula recalculates. As long as the formula exists, it recalculates. But if you copy the combined column and paste as values (removing the formula), it becomes static. Changing the source then does not update the combined value.
This is actually often desirable. You want an address field that is stable even if component parts change. But be intentional about whether you are creating a formula (dynamic, updates when source changes) or a value (static, independent of source).
If you need to update combined values when sources change, keep formulas. If you need stable combined values, paste as values after creating the initial combined column.
Practical Examples of Column Combination
A payroll manager combines first name and last name from separate columns into full name for reports. Uses =TEXTJOIN(” “, TRUE, A:A, B:B) or similar to create the full name field.
An ecommerce manager combines product code, color, and size into a complete SKU (stock keeping unit). Uses =TEXTJOIN(“-“, TRUE, A:A, B:B, C:C) to create product identifiers like “SHIRT-BLUE-LARGE”.
A data analyst combines date parts (year, month, day from separate cells) into a complete date for sorting and analysis. Uses =TEXTJOIN(“-“, TRUE, year_column, month_column, day_column).
A real estate agent combines property address components (street, city, state, zip) into complete addresses for listings. Uses =TEXTJOIN(“, “, TRUE, street, city, state, zip).
These practical examples show column combination solves real business problems: creating identifiers, organizing information, preparing data for reports.
When NOT to Combine Columns
Before combining columns, consider whether combining makes sense. Sometimes keeping columns separate is better. If you combine first and last name, you lose the ability to easily sort by last name or search by last name. Keeping separate columns preserves flexibility.
If you might need to separate the combined value later, combining is risky. Once “John Smith” is combined, extracting just “John” requires formulas like MID or FIND. Keeping separate columns avoids this extraction work.
If data quality is inconsistent (sometimes names have middle initials, sometimes not), combining creates irregular results. Clean data before combining to ensure consistent results.
If you need to update combined values frequently, formulas are better than static values. Combining with formulas maintains the link to source data. Combining with Paste Special values creates independent data that requires manual updates.
Troubleshooting Combination Problems
If your combined result looks wrong, check the formula. Is the separator where you expect? Are the parts in the right order? Copy the formula to a temporary cell and verify it creates the expected result before copying widely.
If combined values do not update when source values change, check whether the combined column contains formulas or values. If values, you need to recreate the column with formulas to get dynamic updates.
If combining creates #VALUE! error, one of the combined parts might be a formula error. Check that all source cells contain valid values, not errors.
If the combined result has extra spaces or irregular formatting, check your separators. Multiple spaces in separators or leading/trailing spaces in source data create irregular output.
Interested in more data manipulation? Explore our guide to splitting columns (the reverse of combining columns). Learn about text functions like MID and FIND for extracting parts of text. Discover how to use concatenation in conditional logic. And check out our article on preparing data for analysis, where combining and splitting columns are essential operations.
