The simplest way to concatenate (join) strings in Excel is to use the ampersand operator (&), like =A1&" "&B1. You can also use the CONCAT function, the older CONCATENATE function, or the TEXTJOIN function for more advanced cases. This article explains each method, when to use it, and how to avoid common errors.
Why Concatenation Matters in Excel
Concatenation is the process of combining two or more text strings into one. In Excel, you might need to join first and last names, combine address parts, or merge data from multiple columns into a single cell for reports, mail merges, or data cleaning. Excel gives you several ways to do this, and choosing the right one depends on your Excel version, the amount of data, and whether you need to handle empty cells or delimiters.
Method 1: The Ampersand Operator (&)
The ampersand is the most direct and widely supported way to concatenate. It works in all versions of Excel, including Excel for the web and Excel for Mac. You simply type an ampersand between each value you want to join.
Example: Suppose cell A2 contains “John” and B2 contains “Doe”. To get “John Doe” in C2, use:
=A2&" "&B2
This formula joins the text in A2, a space (inside quotes), and the text in B2. The result is John Doe. You can also join numbers and dates with text; Excel converts them to text automatically.
Tip: To add a line break between values, use CHAR(10) (Windows) or CHAR(13) (Mac) and enable Wrap Text on the cell. For example: =A2&CHAR(10)&B2.
Method 2: The CONCAT Function
CONCAT is the modern replacement for CONCATENATE. It was introduced in Excel 2016 and is available in Excel 2016, 2019, 2021, and Microsoft 365. It takes multiple arguments and joins them without any delimiter unless you add one manually.
Example: With A2=”John”, B2=”Doe”, the formula:
=CONCAT(A2," ",B2)
returns John Doe. Unlike CONCATENATE, CONCAT also accepts a range of cells. For instance, =CONCAT(A2:A5) joins all values in A2 through A5 with no separator.
Method 3: The TEXTJOIN Function (for Delimiters and Empty Cells)
TEXTJOIN is the most powerful concatenation function, available in Excel 2019, 2021, and Microsoft 365. It lets you specify a delimiter (like a comma or space) and whether to ignore empty cells.
Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Example: If A2:A5 contains “Red”, “Green”, “”, “Blue”, then:
=TEXTJOIN(", ",TRUE,A2:A5)
returns Red, Green, Blue (skipping the empty cell). If you set ignore_empty to FALSE, it would return Red, Green, , Blue.
This function is ideal for joining lists, creating CSV-like strings, or combining names with a comma and space.
Method 4: The CONCATENATE Function (Legacy)
CONCATENATE is the older function, available in all Excel versions but kept for compatibility. It works exactly like CONCAT but does not support ranges. You must list each cell or value separately.
Example: =CONCATENATE(A2," ",B2) returns John Doe. Microsoft suggests using CONCAT instead, as CONCATENATE may be deprecated in future versions, but it still works fine today.
How to Choose the Right Method
- Simple join of a few cells: Use
&– it’s short, fast, and works everywhere. - Joining a range with no delimiter: Use
CONCAT. - Joining a range with a delimiter and ignoring blanks: Use
TEXTJOIN. - Compatibility with old Excel versions (2013 or earlier): Use
CONCATENATEor&.
If you’re using Excel for the web or Excel for Mac, all four methods are available, but TEXTJOIN and CONCAT require Excel 2019 or later on Mac (or Microsoft 365).
Common Problems and How to Fix Them
Problem 1: Numbers appear as text or lose formatting. When you concatenate a number with text, Excel converts the number to text. To keep a specific format (like currency or leading zeros), use the TEXT function. For example, to join a date in A2 (formatted as mm/dd/yyyy) with a name, use:
=A2&" "&TEXT(B2,"mm/dd/yyyy")
Problem 2: Unwanted spaces or #VALUE! errors. If a cell contains an error (like #N/A), concatenation will return that error. Use IFERROR to handle it: =IFERROR(A2,"")&" "&B2.
Problem 3: Leading/trailing spaces in source cells. Use TRIM to clean up: =TRIM(A2)&" "&TRIM(B2).
Problem 4: Very long strings. Excel has a cell character limit of 32,767 characters. If your concatenation exceeds that, you’ll get a #VALUE! error. Split the result into multiple cells.
Frequently Asked Questions
What is the difference between CONCAT and CONCATENATE? CONCAT is the newer function that supports ranges, while CONCATENATE is older and requires individual arguments. Both join text, but CONCAT is more flexible and recommended by Microsoft.
How do I add a line break in concatenated text? Use CHAR(10) on Windows or CHAR(13) on Mac as the delimiter. For example: =A2&CHAR(10)&B2. Then make sure the cell has Wrap Text enabled (Home > Wrap Text).
Can I concatenate a range with a comma and space? Yes, use TEXTJOIN: =TEXTJOIN(", ",TRUE,A2:A10). This is much simpler than using CONCAT with multiple arguments.
Why does my concatenated formula show the formula as text? This usually happens if the cell is formatted as Text. Change the cell format to General, then re-enter the formula (press F2 and Enter).
Is there a shortcut to concatenate in Excel? No, there is no keyboard shortcut for concatenation itself, but you can use the ampersand operator quickly. If you need to combine many cells, consider using Flash Fill (Ctrl+E on Windows, Cmd+E on Mac) to auto-fill based on a pattern.
Alternatives to Formulas: Flash Fill
For one-time concatenation tasks, Flash Fill can be faster than writing formulas. Type the desired result in the first cell (e.g., “John Doe”), select the range, and press Ctrl+E (Windows) or Cmd+E (Mac). Excel detects the pattern and fills the remaining cells. This works best when the data is consistent and you don’t need a dynamic formula that updates when source data changes.
Concatenating with Line Breaks and Special Characters
To include special characters like tabs, use CHAR(9) for a tab. For a line break, use CHAR(10) (Windows) or CHAR(13) (Mac). You can also combine multiple characters: =A2&CHAR(10)&B2&CHAR(10)&C2 creates three lines. Remember to enable Wrap Text to see the line breaks.
Final Thoughts
Choose the method that fits your version and task. For most users, the ampersand operator is the simplest and most reliable. For advanced needs, TEXTJOIN is a game-changer. Always test your formulas with sample data to ensure the output looks correct, especially when dealing with numbers, dates, or empty cells.
Related
- C++ String Data Type: How std::string Works
- Snowflake Split String Into Columns: SQL Methods
- Snowflake SPLIT Syntax: Arrays, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Aggregate Functions in UPDATE: What SQL AllowsUsually, no: SQL does not allow an aggregate such as AVG() or SUM() directly as a row-level value…
- DAX vs Excel Formulas: How They Differ and When to UseDAX and Excel formulas both calculate results, but they work in different places and answer different kinds of…
- Excel SUMIFS Between Dates: Formula, Examples, and TipsTo sum values in Excel for dates from a start date through an end date, use =SUMIFS(C2:C100,A2:A100,">="&E2,A2:A100,"="&E2,A2:A100,"=E2": that…
- Excel Running Total: Formulas, Tables, and TroubleshootingTo create a running total in Excel, enter your amounts in B2:B7, type =SUM($B$2:B2) in C2, and fill…
- Excel Filter Data Validation List: Setup and LimitsYes—updated Microsoft 365 versions of Excel can autocomplete a data-validation dropdown as you type, narrowing the choices to…
- SQL Server String Functions and How to Use ThemSQL Server string functions are built-in T-SQL tools for combining, searching, measuring, extracting, and changing text. Functions such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.