To combine text from two Excel columns, enter a formula such as =A2&" "&B2 in a new column, then fill it down. This joins the values with a space between them. It combines the contents without deleting the original columns, so you can check the results before deciding whether to replace or remove anything.
In Excel, combining text is different from merging cells. The Merge command changes the cell layout and can discard one of the selected cells’ contents. For a list of names, addresses, or other text, use a formula or a text-combining feature instead.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 and two columns containing the text you want to combine.
- An empty column for the combined results. Keeping the source columns intact until you have checked the output is the safest approach.
- A choice of separator, such as a space, comma, hyphen, or no separator. The right choice depends on how the combined text should read.
Step-by-Step
-
Check your source columns. Identify which column’s text should come first and which should follow it. For example, if column A contains first names and column B contains last names, the intended order is first name followed by last name. Check a few rows for blank cells, extra spaces, or inconsistent entries before building the formula. The formula combines what is present; it does not automatically correct misspellings or standardize the source data.
-
Choose an empty destination column. If the source text is in A and B, click C2, assuming row 1 contains headings. Add a heading such as Full Name in C1 if useful. Do not enter the formula over either source column: formulas that refer to those columns need the original values available to calculate correctly.
-
Enter a formula with the separator you want. With first names in A2:A100 and last names in B2:B100, and Ana in A2 and Lopez in B2, enter this in C2:
=A2&" "&B2. It returns Ana Lopez. The ampersand joins text, and the quoted space supplies the gap between the two cell values. This is a straightforward option that works in current Excel versions. -
Use a different separator when needed. The characters between the two sets of quotation marks determine what appears between the values. With North in A5 and Region in B5, this formula returns North – Region:
=A5&" - "&B5. You can put a comma and space between the quotation marks for a result like North, Region, or use empty quotation marks if no separator is needed. Make sure any punctuation and spaces are inside the quotation marks. -
Handle blank cells if your data has them. A basic formula such as the first-name example can leave an unwanted leading or trailing space when one of its source cells is empty. With A3 blank and B3 containing Lee, enter this formula in C3:
=TEXTJOIN(" ",TRUE,A3,B3). It returns Lee, omitting empty cells and inserting a space between nonempty values. TEXTJOIN is available in Microsoft 365. If your regional Excel settings use semicolons as argument separators, replace the formula’s commas with semicolons. -
Fill the formula down the column. Select the cell containing the formula. Point to the small square at the bottom-right corner of the selected cell, called the fill handle, and drag it down through the rows you need. If the adjacent source column is filled continuously, double-clicking the fill handle can fill the formula down to the neighboring data range. Check the last row afterward to make sure the fill reached every record. Excel adjusts the row references automatically, so a formula in C3 refers to A3 and B3.
-
Review the combined results. Look at the first few rows, several rows in the middle, and the last row. Check that the order, spacing, punctuation, and treatment of blanks are right. If the result is wrong, correct the formula in the first result cell and fill it down again. Because the formula points to the source cells, changes to those source values will update the combined results.
-
Make the results permanent if needed. If you need a fixed text column rather than formulas, select the completed results and copy them. Choose an empty destination or select the same result range, then use Home > Paste > Values. This replaces formulas with their current displayed text. Confirm the pasted values before deleting or overwriting the original source columns. On Windows, Ctrl+C copies and Ctrl+V pastes; on Mac, use Command+C and Command+V. The ribbon’s Paste Values option is available on both platforms, though its exact presentation can vary by Excel version.
Common Mistakes
Using Merge & Center to combine text. This command merges the selected cells into a single cell; it does not join both cell contents. If multiple selected cells contain data, Excel typically keeps only the upper-left cell’s content and warns that other contents will be lost. Cancel if you see that warning and use a formula in a separate column instead.
Forgetting to add a separator. The formula =A2&B2 joins text directly. If A2 contains Mia and B2 contains Chen, the result is MiaChen, not Mia Chen. Put the desired space or punctuation in quotation marks between the cell references.
Using the wrong separator or order. A comma with no following space can produce text that looks cramped, while placing a space outside the quotation marks may cause a formula error. Confirm which item should come first, and inspect the exact characters between the quotes.
Deleting source data too soon. A formula-based result depends on the original cells. If you remove or overwrite them before converting the results to values, the combined column can show errors or change. Copy and paste values first, then verify the output before removing source columns.
Filling only part of the list. A formula in the first row does not combine every row by itself unless you fill it down. Check the bottom of the range, especially if there are blank rows that can interrupt double-clicking the fill handle.
Tips
Pick a separator based on how the result will be used. A single space usually suits names, a comma and space can suit city-and-region text, and a hyphen can make codes easier to scan. If the combined values are going into another system, check that system’s preferred format before adding punctuation.
Keep the source columns until you have checked the results and saved a copy of the workbook. If you plan to paste values over the formula column, you can also first paste them into a separate empty column and compare the two. This gives you a simple fallback if you notice a formatting or data issue later.
For a small range, drag the fill handle. For a long range, double-clicking it is faster when the neighboring column has continuous data. If the range is formatted as an Excel table, entering a formula in the first row of a new table column will usually fill that formula through the table column automatically; still check that every row is included.
Remember that formulas join the displayed cell values, but may not preserve the original formatting of numbers or dates. For example, a date can appear as its underlying serial number when joined with text. If you need a particular date or number format in the combined result, format the value within the formula using Excel’s TEXT function, or convert and check the output carefully before distributing it.
If you only need a one-time combination and do not need a reusable formula, Excel’s Flash Fill may be an option: type the desired combined result in the first row, then choose Data > Flash Fill. On Windows, Ctrl+E is the Flash Fill shortcut. Review the filled entries because Excel infers the pattern from examples, and its interpretation may not be right for every row. Flash Fill produces text rather than formulas, so later edits to the source columns will not update those results.
Troubleshooting
The formula appears as text instead of calculating. The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, re-enter the formula, and press Enter. If you see the formula in the cell while other formulas calculate, check whether Show Formulas is turned on; toggle it from the Formulas tab or press Ctrl+` on Windows. The key combination can differ on Mac keyboards.
Excel reports a formula error. Check that the formula starts with an equals sign, that each quotation mark has a matching partner, and that the cell references are valid. If a formula with commas between function arguments is rejected, your regional settings may require semicolons instead. The basic ampersand formula does not use function arguments, so it avoids that particular separator issue.
The result has an unexpected gap. A plain space-joining formula adds a space even if one source cell is blank. Use TEXTJOIN with the ignore-empty option, or inspect the source cells for spaces that are present but hard to see. If your Excel version does not include TEXTJOIN, you can use an IF formula: with A4 and B4 as the two source cells, and A4 blank while B4 contains Lee, enter =IF(A4="",B4,IF(B4="",A4,A4&" "&B4)). It returns Lee and avoids an extra space when either cell is empty.
Some rows did not get a result. Select the last formula cell and inspect its references. If the fill stopped at a blank neighboring row, drag the fill handle through the remaining range, or copy the formula cell and paste it into the missing destination cells. Confirm that each row points to its own pair of source cells rather than repeating the same row.
Dates or numbers look different after combining. Joining text can change how a number or date appears because Excel may use its underlying value rather than the display format you see in the source cell. If the output must show a particular format, check the result in a few rows and adjust the formula to format that value as text before combining it. Avoid deleting the original columns until the displayed output is correct.
Frequently Asked Questions
Does merging two columns delete the original text? A formula in a new column leaves the original text unchanged. The Merge & Center command is different and may discard contents from cells other than the upper-left cell, so do not use it to combine text.
Can I combine two columns without a space? Yes. Join the cell references directly with an ampersand, with no quoted space between them. For example, values Mia and Chen would become MiaChen. Add punctuation or spaces in quotation marks only when you want them in the result.
Can I combine more than two columns? Yes. Add more cell references and separators to an ampersand formula, or use TEXTJOIN to combine several cells with one separator. Check the order of the references and decide how blank cells should be handled before filling the formula down.
Will the combined column update when I edit the source text? Yes, if it contains formulas. Excel recalculates the result when a referenced cell changes. If you paste the results as values, they become fixed text and no longer update when the source cells change.
Can I combine columns in Excel for Mac or Excel for the web? Yes. The ampersand and TEXTJOIN formulas work in Microsoft 365 for Mac and Excel for the web as well as Windows. Ribbon layout and keyboard shortcuts can vary, but you can enter the formula in a new cell and fill it down in each version.
Related
- Excel Flash Fill in Polish: Name and How to Use the Feature
- Flash Fill Excel Practice: Examples and Tips
- Flash Fill in Excel: How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Count Rows in Excel: Formulas and Quick MethodsTo count records in Excel, use =COUNTA(A2:A20) when each record has a filled-in value in column A; it…
- Excel Right-Click Shortcut: Open the Context MenuIn Excel for Microsoft 365 on Windows, select a cell or range and press Shift+F10 to open its…
- OVER() in Snowflake: Window Function Syntax & ExamplesIn Snowflake, OVER() is the clause that turns a regular aggregate function (like SUM, COUNT, or AVG) into…
- RIGHT and SUBSTITUTE in Excel: Extract a Last NameTo return the final word in a full name in Excel, enter =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2)))) in B2, assuming…
- INDIRECT in Excel: Create Dynamic Cell ReferencesIn Excel, INDIRECT turns text that looks like a cell or range address into a usable reference. For…
- Microsoft Excel: How to Sort Dates in a Table EasilyTo sort dates in Microsoft Excel for Microsoft 365 on Windows, select a cell in your date column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.