If you searched for “how to merge two columns in excel with text”, you need a practical method for joining the contents of two columns into one, and this guide gives you several, from the simplest formula to the fastest built-in tools. Learning how to merge two columns in excel with text is one of the most useful spreadsheet skills you can pick up, because real-world data so often arrives split apart: first names in one column and surnames in another, town names in one column and postcodes in another, or product names and codes that need to become a single label. The methods below work in modern versions of Excel and in most cases in Google Sheets too, and each one is explained with the exact formula or steps, so you can copy it straight into your own workbook.
Before you start, it helps to understand what “merging” means here, because Excel uses the word in two different ways. The Merge and Centre button on the Home tab joins cells into one big cell, but it keeps only the contents of the top-left cell and discards the rest, which is almost never what you want when combining text. What you actually want is concatenation: joining the text values of two cells into a single text string, usually with a space or another separator between them. That distinction is the first thing to grasp when learning how to merge two columns in excel with text. Every method in this guide does concatenation, not cell merging, and knowing the difference will save you from losing data. When people ask how to merge two columns in excel with text, concatenation is the answer they are looking for, even if they use the word merge.
How to merge two columns in Excel with text using the ampersand
The simplest and most reliable way to learn how to merge two columns in excel with text is the ampersand operator, written as &. The ampersand joins two values together, and you can add a separator between them by putting it in quotation marks. Suppose first names are in column A and surnames are in column B, starting in row 2. In cell C2, type =A2&” “&B2 and press Enter. Excel will display the full name with a space in the middle. The formula reads naturally once you know the pattern: take the value in A2, add a space, then add the value in B2. Copy the formula down the column by dragging the fill handle, the small square at the bottom-right corner of the cell, and every row will be combined the same way. Once you can write that formula from memory, you know how to merge two columns in excel with text for any pair of columns.
The ampersand method is flexible, because you can add any separator you like. Use “, ” for a comma and a space, ” – ” for a hyphen with spaces, or “” for no separator at all when you want the two values joined directly. You can also combine more than two columns by chaining ampersands, for example =A2&” “&B2&” “&C2 to join three columns. If one of your columns contains numbers, Excel will convert them to text automatically in the result, which is convenient for codes and reference numbers. The one thing to watch is blank cells: if a cell is empty, the ampersand still adds the separator, so you can end up with a stray space or comma. A later section explains how to handle blanks cleanly.
How to merge two columns in Excel with text using CONCAT and TEXTJOIN
Excel also provides dedicated functions for joining text, and they are worth knowing because they handle larger jobs more neatly. The CONCAT function, available in Excel 2019 and later and in Microsoft 365, joins a list of values: =CONCAT(A2:B2) combines the two cells, although you still need to add a separator yourself, for example =CONCAT(A2,” “,B2). The TEXTJOIN function is more powerful still, because it lets you specify a separator once and apply it across a whole range: =TEXTJOIN(” “,TRUE,A2:B2) joins the two cells with a space, and the TRUE argument tells Excel to ignore empty cells, which neatly solves the blank-cell problem mentioned above. If you are learning how to merge two columns in excel with text for a large dataset, TEXTJOIN is usually the cleanest formula to reach for.
There is also an older function called CONCATENATE, which works the same way as CONCAT but is kept mainly for compatibility with older workbooks. New workbooks should use CONCAT or TEXTJOIN instead, because CONCATENATE cannot handle ranges as flexibly and is no longer the recommended function. A quick comparison helps you choose: use the ampersand for quick one-off joins, CONCAT for simple joins across a range, and TEXTJOIN when you need a separator and want blanks ignored. All three produce live formulas, which means the combined column updates automatically if you edit the source columns, and that live link is usually an advantage rather than a drawback. Whichever function you pick, the goal is the same: a reliable method showing how to merge two columns in excel with text that updates when source data changes.
| Method | Example formula | Best for |
|---|---|---|
| Ampersand (&) | =A2&” “&B2 | Quick joins, custom separators, any Excel version |
| CONCAT | =CONCAT(A2,” “,B2) | Simple joins in Excel 2019 and later |
| TEXTJOIN | =TEXTJOIN(” “,TRUE,A2:B2) | Ranges, separators, and ignoring blank cells |
| CONCATENATE | =CONCATENATE(A2,” “,B2) | Compatibility with very old workbooks |
| Flash Fill | No formula; type an example and press Ctrl + E | One-off joins without formulas |
How to merge two columns in Excel with text using Flash Fill
If you prefer not to write formulas at all, Flash Fill is the fastest option for a one-off task. Type the combined result you want in the first row of your new column, for example the full name for the first person, then start typing the second row and watch Excel suggest the pattern for the rest of the column. Press Enter to accept, or select the column and press Ctrl + E to trigger Flash Fill manually. Flash Fill works by detecting the pattern in your example, so it handles spaces and separators well, and it is ideal when you need a quick result for a report or a mail merge. The trade-off is that Flash Fill produces static text, not formulas: if you later edit the source columns, the combined column will not update, so you would need to run Flash Fill again. For a single report, that speed is why many users prefer Flash Fill when they need to know how to merge two columns in excel with text in a hurry.
Flash Fill has a few quirks worth knowing. It works best when your example is clear and consistent, and it can struggle if the source data is irregular, for example names with middle initials in some rows but not others. If Flash Fill produces a wrong guess, undo it and give a second example row to clarify the pattern before trying again. Also note that Flash Fill is available in Excel 2013 and later on Windows, and in more recent Mac versions, so very old installations may not have it. For most everyday jobs, though, it is the quickest route to learning how to merge two columns in excel with text without touching a formula.
Merging columns with Power Query for repeated reports
When you repeat the same combination every week or every month, Power Query is the professional choice. Power Query is Excel’s data transformation tool, found on the Data tab, and it lets you build a repeatable process: load your table, select the two columns, choose Merge Columns from the Transform tab, pick a separator, and name the new column. Each time fresh data arrives, you refresh the query and the combination is rebuilt automatically. This removes the copy-paste errors that creep into manual work, and it keeps a clear record of exactly what transformation was applied. That repeatability is the real advantage for anyone who regularly needs to know how to merge two columns in excel with text for reporting. For anyone who regularly asks how to merge two columns in excel with text as part of a reporting routine, investing an hour in Power Query pays for itself quickly.
Power Query also handles messy data better than formulas in some cases, because you can add steps to trim extra spaces, change capitalisation, or remove blank rows before merging. The merged result can then be loaded back into a worksheet or into Excel’s data model for further analysis. The main drawback is the learning curve: Power Query has its own interface and vocabulary, and it is overkill for a single small table. Use formulas for one-off jobs and small datasets, and reserve Power Query for recurring reports and larger data preparation tasks.
Handling blanks, numbers, and common errors
Real data is rarely tidy, so it pays to know how the methods above behave with awkward inputs. Blank cells are the most common issue: the ampersand method adds separators even for empty cells, producing results like “Smith ” with a trailing space or “Paris, ” with a trailing comma. TEXTJOIN with its ignore-empty argument avoids this cleanly, and a TRIM wrapper, =TRIM(A2&” “&B2), removes leading, trailing, and double spaces from an ampersand result. Numbers need care too: Excel converts numbers to text when concatenating, but it drops number formatting, so a date or a currency value may appear as a raw serial number. A quick check of the first and last rows will confirm that your method for how to merge two columns in excel with text handled blanks correctly. The TEXT function fixes this, for example =A2&” “&TEXT(B2,”DD/MM/YYYY”) formats a date properly inside the combined string.
Two other errors catch beginners out. First, forgetting the quotation marks around a separator produces an error, because Excel treats unquoted text as a name or reference. Second, copying a formula down a column with relative references is usually what you want, but if your separator or a fixed cell should not move, lock it with dollar signs, as in $A$1. Finally, remember that a formula result is not the same as typed text: if you need to send the combined column to someone else, or sort and filter it independently, convert formulas to values first by copying the column and using Paste Special followed by Values. That step is essential before deleting the original columns, because deleting source cells will break every formula that points at them.
Frequently asked questions about merging columns in Excel
What is the easiest way to merge two columns in Excel with text?
For most people, the ampersand formula =A2&” “&B2 is the easiest: it works in every version of Excel, it is easy to remember, and it handles separators exactly as you specify them. Either way, the skill of how to merge two columns in excel with text is one you will reuse constantly. Flash Fill is even quicker for one-off jobs if you do not need a live formula.
Can I merge two columns without losing data?
Yes, provided you use concatenation formulas or Flash Fill rather than the Merge and Centre button, which discards all but the top-left cell. Always work in a new column and keep your original columns until you have checked the result.
How do I merge columns and keep the result when I delete the originals?
Copy the combined column, then use Paste Special and Values to replace the formulas with static text. Only then delete the original columns, because the formulas depend on them.
Why does my merged text show numbers instead of dates?
Because concatenation converts values to plain text and drops formatting. Wrap the date or number in the TEXT function with your preferred format, such as TEXT(B2,”DD/MM/YYYY”), to keep it readable.
Conclusion: pick the method that fits the job
There is no single best answer to how to merge two columns in excel with text, because the right method depends on the job. Use the ampersand for quick, flexible joins; use TEXTJOIN when you need a separator across a range and want blanks ignored; use Flash Fill for fast one-off tasks; and use Power Query when the same combination must be rebuilt regularly. Whichever method you choose, work in a new column, check the first few rows carefully, and convert formulas to values before deleting your sources. With those habits, combining columns becomes a routine two-minute task rather than a source of lost data. Practise each method once on a copy of real data, and how to merge two columns in excel with text will become second nature. For more spreadsheet and maths guides, see our explainers on defining the mean in maths and working with stock market data.
