You need to combine text from different cells or add separators in Excel. You have three main formula options: the ampersand operator, CONCAT, and CONCATENATE. Each method has different capabilities and compatibility with Excel versions. This article explains the technical differences and shows you which function to use for your specific task.
Key Takeaways: Choosing a Text Joining Method in Excel
- The & operator: This is the fastest and most flexible way to join a few text strings or cell references in any version of Excel.
- CONCAT function: Use this modern function to combine a range of cells or a large list of items without typing each reference.
- CONCATENATE function: This older function is kept for compatibility; avoid it for new work as it lacks the range feature of CONCAT.
How Excel Text Joining Functions Work
Excel provides multiple tools to merge text, each with a distinct purpose. The ampersand symbol is an operator, not a function. You use it directly within a formula like =A1&” “&B1. It connects every item you specify. The CONCAT and CONCATENATE functions are built-in Excel functions that follow the syntax =CONCAT(text1, [text2], …).
The key technical difference is in how they handle cell ranges. The CONCAT function, introduced in Excel 2016, accepts a range reference like A1:A10. It will join all values from those ten cells in order. The older CONCATENATE function does not accept a range. You must list every cell individually: CONCATENATE(A1, A2, A3, A4, A5). This makes it inefficient for combining many cells.
Version Compatibility and Deprecation
CONCATENATE is still available in all current Excel versions for backward compatibility. However, Microsoft lists it in the “Compatibility functions” category and recommends using CONCAT for new spreadsheets. If you share files with users on Excel 2013 or earlier, they will not have the CONCAT function. In that case, you must use & or CONCATENATE to ensure your formulas work for everyone.
Steps to Use Each Text Joining Method
Method 1: Using the Ampersand Operator
- Start a formula with an equals sign
Click on the cell where you want the combined text to appear. Type the equals sign to begin your formula. - Add the first text item or cell reference
Type the reference of the first cell, like A2. Or, type text inside double quotes, like “Project: “. - Type the & symbol
Press Shift + 7 on your keyboard to insert the ampersand after your first item. - Add the next item and any separators
Add the next cell reference or text string. To insert a space or comma, type &” “& or &”, “&. Repeat this step for all items you need to join. - Press Enter to complete the formula
Check the cell result. For example, =A2&”, “&B2 will combine the contents of A2 and B2 with a comma and space.
Method 2: Using the CONCAT Function
- Type =CONCAT( to start the function
In your result cell, type =CONCAT( . Excel will show a tooltip with the function syntax. - Enter your text arguments
Click on a single cell, or click and drag to select a range like C5:C12. You can also type text in quotes: =CONCAT(“ID: “, A5). - Close the parentheses and press Enter
Type ) and then press Enter. Excel will join all items in the order you provided. No separators are added automatically. - Add delimiters using the function
To add spaces or commas between range values, you cannot do it within a single range argument. You must use a different method, like the TEXTJOIN function, or reference a range with the delimiter already in adjacent cells.
Method 3: Using the CONCATENATE Function
- Type =CONCATENATE( in a cell
This function works identically to CONCAT in its basic form for individual items. - List each text item separately
Click on individual cells, separating each reference with a comma. You cannot select a whole range like A1:A10. - Close the formula and view the result
Press Enter. The cell will display the combined text. For example, =CONCATENATE(D2, ” “, E2) joins two cells with a space.
Common Mistakes and Limitations to Avoid
Forgetting to Add Spaces Between Joined Words
A common result error is text running together like “JohnSmith”. None of these methods add separators automatically. You must explicitly include them as a text string argument. When using the & operator, remember to add &” “& between cell references. In CONCAT or CONCATENATE, add ” ” as an extra argument between your cell references.
CONCAT Does Not Ignore Empty Cells in a Range
If you use CONCAT to join a range containing blank cells, those blanks are treated as zero-length strings. They do not create extra spaces, but they also do not create separators. This can lead to unexpected results if your data has gaps. For example, joining the range A1:A3 where A2 is blank results in the value from A1 immediately followed by the value from A3. Use the TEXTJOIN function if you need to ignore empty cells completely.
Using CONCATENATE for Large Data Sets
Because CONCATENATE requires each cell to be listed individually, it is a poor choice for joining many cells. Writing a formula with 50 separate arguments is error-prone and difficult to edit. If you are using an older version of Excel that lacks CONCAT, consider using the & operator in a more creative way, or explore alternative methods like using the Power Query editor to merge columns.
Text Joining Methods: Feature Comparison
| Item | & Operator | CONCAT Function | CONCATENATE Function |
|---|---|---|---|
| Accepts a cell range reference | No | Yes | No |
| Available in Excel 2010/2013 | Yes | No | Yes |
| Ease of adding custom separators | High | Medium | Medium |
| Recommended for new work | Yes (for simple joins) | Yes (for ranges) | No |
| Maximum number of arguments | Limited by formula length | 254 | 254 |
You can now choose the correct tool to merge text in your worksheets. For most tasks, use the & operator for its simplicity or CONCAT for its range capability. Remember that the TEXTJOIN function is a more powerful alternative when you need delimiters and the ability to skip blanks. Try using the F9 key in the formula bar to evaluate parts of your & operator formula and check each segment’s result.