If you have ever needed to join first and last names, combine a city with a postcode, or merge product codes in a spreadsheet, you have needed to excel concatenate two columns. Learning to excel concatenate two columns is one of the most useful everyday spreadsheet skills, because it turns messy, split data into clean, readable results without retyping anything. Once you can excel concatenate two columns fluently, repetitive data clean-up takes minutes instead of hours. Whether you use Microsoft Excel, Google Sheets, or another spreadsheet app, the same basic idea applies: take the text from one column, join it to the text from another column, and place the combined result in a new column. This guide walks through every reliable method, step by step, with examples you can copy.
The task to excel concatenate two columns sounds simple, and it is, once you know the right formula. The confusion usually comes from the choices: the CONCATENATE function, the newer CONCAT and TEXTJOIN functions, the ampersand operator, and Flash Fill all do similar jobs, but they behave differently with spaces, blanks, and separators. Choosing the wrong method can leave you with names squashed together, missing spaces, or formulas that break when data changes. This guide compares the methods, shows exactly what to type, and explains how to handle common problems such as extra spaces, blank cells, and numbers that lose their formatting.
Excel Concatenate Two Columns: The Basics
To excel concatenate two columns in the simplest way, you use a formula in a new column that refers to the two cells you want to join. Suppose first names are in column A and last names are in column B, starting in row 2. In cell C2, you would type a formula that joins A2 and B2 with a space between them. Copy that formula down the column, and every row gets its own combined result. The original columns stay untouched, which is important: your formula column updates automatically if a name is corrected, while a manual retype would quickly go out of date.
The key detail is the separator. Joining two cells without a separator produces a single run of text with no gap, such as JohnSmith instead of John Smith. Most real tasks need a space, a comma, a hyphen, or a line break between the parts. In a formula, a separator is written as text in double quotes, such as ” ” for a space or “, ” for a comma followed by a space. Getting the separator right is the difference between a clean result and a messy one, and it is the detail beginners most often forget. Once you understand separators, every concatenation method below becomes easy to adapt.
Setting Up Your Example
Imagine a small table with first names in column A, last names in column B, and a blank column C headed Full Name. Row 2 holds John in A2 and Smith in B2. Row 3 holds Maria in A3 and Garcia in B3. Your goal is a full name in column C for each row. Every method in this guide uses this same layout, so you can try each one on the same data and compare the results. Keep the original columns as they are, and always put the combined result in a new column: that way you can check your work, undo mistakes, and keep the source data safe.
Method 1: The Ampersand Operator to Excel Concatenate Two Columns
The quickest way to excel concatenate two columns is the ampersand operator, written as the symbol between cell references. In cell C2, type =A2&” “&B2 and press Enter. The result is John Smith. The ampersand joins the value in A2, then a space in quotes, then the value in B2. Copy the formula down to C3 and beyond by dragging the small fill handle at the bottom-right corner of the cell, or by double-clicking it. Each row updates its own references automatically, so C3 becomes Maria Garcia without any extra typing.
The ampersand method is flexible. You can join more than two columns by adding more ampersands, and you can mix cell references with fixed text. For example, =A2&”, “&B2 produces Smith, John style output if the columns are ordered differently, while =”Name: “&A2&” “&B2 adds a label in front of every result. You can also join text with numbers, though numbers may lose special formatting, a problem covered later in this guide. For most everyday tasks, the ampersand is the method experienced spreadsheet users reach for first, because it is short, readable, and works in Excel, Google Sheets, and most other spreadsheet apps.
Ampersand Tips and Variations
When you excel concatenate two columns with ampersands, remember that every piece of fixed text needs its own pair of double quotes. A missing quote is the most common cause of a formula error. Also remember that the formula must start with an equals sign, and that spaces inside the quotes are part of the result: ” ” gives one space, while ” ” gives two. If your result shows a formula instead of a value, check that the cell is formatted as General rather than Text, and that you have not accidentally typed a leading apostrophe. These small checks solve most beginner problems in seconds.
Method 2: The CONCATENATE Function
The CONCATENATE function is the classic, named way to excel concatenate two columns. In cell C2, type =CONCATENATE(A2,” “,B2) and press Enter. The result is the same as the ampersand method: John Smith. Inside the brackets, you list each piece in order, separated by commas: the first cell, the separator in quotes, and the second cell. The function joins them in exactly that order. In Google Sheets, the same function works identically, which makes it a safe choice when sharing files between Excel and Sheets users.
CONCATENATE has one limitation worth knowing: it does not ignore blank cells or add separators automatically. If a middle name column is blank for some rows, you can end up with double spaces in the result, and you must handle that with extra logic or by cleaning the data first. Microsoft now recommends the newer CONCAT and TEXTJOIN functions for new work, but CONCATENATE remains fully supported and widely understood. If you open an older spreadsheet and see CONCATENATE formulas, there is no need to replace them: they still work, and they are easy to read. That readability is one reason many teams still excel concatenate two columns with CONCATENATE in shared files. For more step-by-step guides, see our comprehensive guide for practical how-to explanations.
Method 3: CONCAT and TEXTJOIN for Modern Excel
Modern versions of Excel offer CONCAT and TEXTJOIN, which improve on CONCATENATE in useful ways. CONCAT works like CONCATENATE but accepts a range as well as individual cells, so =CONCAT(A2:B2) joins the two cells, though without a separator unless you add one. TEXTJOIN is the more powerful option: =TEXTJOIN(” “,TRUE,A2:B2) joins the range with a space separator and, because the second argument is TRUE, ignores any blank cells. That single feature solves the double-space problem that affects the older methods, making TEXTJOIN the best choice when some rows have missing middle names, suffixes, or optional fields.
TEXTJOIN also handles ranges gracefully. If you need to join three, four, or more columns, you can pass the whole range, such as A2:D2, instead of listing every cell. The separator is defined once, at the start of the formula, which keeps long formulas readable. Note that TEXTJOIN is available in newer Excel versions and in Google Sheets, but very old Excel versions do not have it. If you share files with users on older software, the ampersand or CONCATENATE method is the safer, more compatible choice. When compatibility is not a concern, TEXTJOIN is usually the cleanest way to excel concatenate two columns and more.
| Method | Example Formula | Best For |
|---|---|---|
| Ampersand | =A2&” “&B2 | Quick joins, all spreadsheet apps |
| CONCATENATE | =CONCATENATE(A2,” “,B2) | Classic files, easy reading |
| CONCAT | =CONCAT(A2:B2) | Joining ranges in modern Excel |
| TEXTJOIN | =TEXTJOIN(” “,TRUE,A2:B2) | Skipping blanks, joining many columns |
| Flash Fill | Type example, press Ctrl+E | One-off joins without formulas |
Method 4: Flash Fill for One-Off Joins
Flash Fill is a different approach to excel concatenate two columns: instead of writing a formula, you type the result you want for the first row, and Excel guesses the pattern for the rest. Type John Smith in C2, start typing Maria Garcia in C3, and Excel will suggest the full column. Press Enter to accept, or use the shortcut Ctrl+E after typing the first example. Flash Fill is fast and needs no formula knowledge, which makes it ideal for a one-off clean-up job where the result will not need to update later.
The trade-off is that Flash Fill produces static text, not live formulas. If a name in column A or B changes later, the Flash Fill result will not update, while a formula result will. Live formulas are why most professionals excel concatenate two columns with formulas rather than retyping. Flash Fill can also misread unusual patterns, especially with inconsistent spacing, initials, or hyphenated names, so always check the whole column before trusting it. For a quick, throwaway task, Flash Fill is excellent. For a spreadsheet you will reuse, update, or share, a formula method is the more reliable choice, because the combined column stays linked to its source data.
Excel Concatenate Two Columns: Fixing Common Problems
Several common problems appear when you excel concatenate two columns, and all of them have simple fixes. Extra spaces are the most frequent: if a source cell contains a leading or trailing space, the result will have a double space. Wrap each reference in the TRIM function, such as =TRIM(A2)&” “&TRIM(B2), to remove those stray spaces. Blank cells cause similar trouble: a missing last name can leave a trailing space, which TEXTJOIN avoids, or which you can handle with an IF check that only adds the separator when the second cell is not empty.
Numbers and dates need special care. Joining a number directly can strip its formatting, turning a postcode with a leading zero into a shorter number, or a date into a serial number. The TEXT function fixes this by formatting the value as text first, for example =A2&” “&TEXT(B2,”0.00″) for a currency-style number, or =TEXT(B2,”dd/mm/yyyy”) for a date, using the format that matches your locale. If a formula shows an error, check for missing quotes, missing commas, or a misspelt function name. If the result looks like a formula rather than a value, check the cell format. Most errors in concatenation are typing errors, not conceptual ones, and they are fixed by reading the formula slowly from left to right. For related how-to reading, see our writing guide for clear, structured explanations. Clear explanations help, but practice is how you truly learn to excel concatenate two columns.
Converting Formulas to Final Values
Once your combined column is correct, you may want to turn the formulas into plain values, for example before deleting the original columns or sending the file to someone else. Select the formula column, copy it, then use Paste Special and choose Values to replace the formulas with their results. Only do this after checking the results carefully, and only delete the original columns after the values are safely pasted. A safer habit is to keep the original columns hidden rather than deleted, so the source data remains available if a correction is needed later. If you must delete them, keep a backup copy of the file first.
Frequently Asked Questions About Concatenating Columns
How do I excel concatenate two columns with a comma?
Use a comma and a space as the separator: =A2&”, “&B2 or =CONCATENATE(A2,”, “,B2). For a last-name-first format, reverse the references: =B2&”, “&A2 produces Smith, John from the same data.
Why do numbers lose formatting when concatenated?
Formulas join the underlying value, not the displayed format. Use the TEXT function to impose a format, such as TEXT(B2,”0.00″) for two decimal places, so the combined result keeps the appearance you expect.
Does this work in Google Sheets?
Yes. The ampersand, CONCATENATE, CONCAT, and TEXTJOIN methods all work in Google Sheets, and the formulas in this guide can be copied across with almost no change. Flash Fill is an Excel feature, but Sheets offers a similar Smart Fill suggestion in many cases.
Conclusion: The Best Way to Excel Concatenate Two Columns
The best way to excel concatenate two columns depends on your situation, but the core skill is the same in every method: join the first cell, add a separator in quotes, and join the second cell, all inside a formula in a new column. The ampersand is fastest for everyday work, CONCATENATE is clearest for shared classic files, TEXTJOIN is best when blanks are involved, and Flash Fill is ideal for quick one-off jobs. Knowing all four means you can pick the right tool instead of forcing one method into every situation.
Start with the ampersand on a copy of your data, check the separator and the spacing, handle blanks and number formatting with TRIM and TEXT where needed, and convert to values only when the results are verified. With that workflow, the task to excel concatenate two columns stops being a repetitive typing chore and becomes a fast, repeatable formula you can apply to names, addresses, codes, and any other split data. Practice on a small table first, and you will soon join columns confidently in any spreadsheet. From names to codes, the ability to excel concatenate two columns is a skill that pays off daily.
