This guide shows you how to combine text from multiple cells into one cell using Excel’s CONCATENATE and CONCAT functions. You’ll learn the exact syntax, how to add spaces and punctuation, and what to do when the newer CONCAT function behaves differently. No prior formula experience is needed, just a spreadsheet with some text you want to merge.
What You’ll Need
- Microsoft Excel (2016 or later for CONCAT; CONCATENATE works in all versions)
- A worksheet with at least two cells containing text, numbers, or dates you want to combine
- Basic familiarity with typing formulas into a cell (starting with =)
Step-by-Step
- Open your Excel workbook and navigate to the sheet containing the data you want to combine. For example, you might have first names in column A and last names in column B.
- Decide which function to use. If you have Excel 2016 or later, use
CONCATbecause it supports ranges (e.g.,A1:A3) and is the modern replacement. If you have an older version or need maximum compatibility, useCONCATENATE. - Click the cell where you want the combined text to appear. This is usually to the right of your data or on a separate row.
- Type the formula. Start with an equals sign, then the function name, then an opening parenthesis. For example:
=CONCAT(or=CONCATENATE(. - Add your first text reference. Click the cell containing the first piece of text, or type its address (e.g.,
A2). If you want to include literal text, enclose it in double quotes, like"Mr. ". - Add a comma to separate arguments. Each piece of text, cell reference, or range must be separated by a comma. For CONCATENATE, you list items one by one:
=CONCATENATE(A2, B2). For CONCAT, you can also use a range:=CONCAT(A2:B2). - Add the rest of your items. Continue adding commas and references. To insert a space between two cells, add a comma, then
" "(a space inside quotes), then another comma. Example:=CONCATENATE(A2, " ", B2). - Close the parenthesis and press Enter. The combined text appears in the cell. If you used a range with CONCAT, it reads left-to-right, top-to-bottom.
- Fill down if needed. If you have multiple rows, double-click the fill handle (the small square at the bottom-right of the selected cell) to copy the formula down the column.
- Format numbers and dates. If you combine a number or date, Excel may show its raw serial value (e.g., 45213 for a date). To keep the original format, use the
TEXTfunction inside your formula:=CONCATENATE(A2, " ", TEXT(B2, "mm/dd/yyyy")).
Common Mistakes
- Forgetting the space.
=CONCATENATE(A2,B2)gives “JohnDoe” with no space. Always add" "as a separate argument if you need a space. - Using commas instead of semicolons. In some regional Excel settings, list separators are semicolons (;). If your formula returns a #NAME? error, try replacing commas with semicolons.
- Quoting cell references. Writing
"A2"treats it as literal text, not the cell’s value. UseA2without quotes. - Using CONCAT with a single cell and a range incorrectly. CONCAT requires at least one argument, but you can mix ranges and single cells:
=CONCAT(A2, " ", B2:C2)works, but=CONCAT(A2:B2:C2)is invalid because ranges must be contiguous and rectangular. - Expecting CONCAT to ignore empty cells. CONCAT does not skip blanks; it includes them as empty strings. If you want to skip blanks, you need a more complex formula with IF or TEXTJOIN (Excel 2019+).
Tips
- Use the ampersand (&) operator for quick joins. Instead of a function, you can write
=A2 & " " & B2. It works in all Excel versions and is often shorter. - Add line breaks. Use
CHAR(10)to insert a line break within the combined text. For example:=CONCATENATE(A2, CHAR(10), B2). Make sure the cell has Wrap Text enabled to see the break. - Combine a range with CONCAT. If you have a list of items in A1:A5,
=CONCAT(A1:A5)joins them all without separators. To add separators, you’d need TEXTJOIN. - Use TEXTJOIN for delimiters. In Excel 2019 and Microsoft 365, TEXTJOIN lets you specify a delimiter and ignore empty cells:
=TEXTJOIN(", ", TRUE, A1:A5). - Convert formulas to values. If you want to remove the formula and keep only the combined text, copy the cell, right-click, and choose Paste Special > Values.
Troubleshooting
- #NAME? error. This usually means the function name is misspelled or your Excel version doesn’t support CONCAT. Check the spelling, or use CONCATENATE instead.
- #VALUE! error. This occurs when a referenced cell contains an error, or when you try to combine a range with CONCATENATE (which doesn’t accept ranges). Use CONCAT for ranges, or list each cell individually.
- Formula shows as text. If the cell displays the formula exactly (e.g., =CONCAT(A2,B2)) instead of the result, the cell is formatted as Text. Select the cell, change the number format to General, then press F2 and Enter.
- Date appears as a number. As mentioned, wrap dates in TEXT with a format code. For example:
=CONCATENATE("Order date: ", TEXT(C2, "mmm d, yyyy")). - Spaces are missing or extra. Check that you added the space argument correctly. If you have leading/trailing spaces in source cells, use TRIM:
=TRIM(CONCATENATE(A2, " ", B2)).
Frequently Asked Questions
What is the difference between CONCAT and CONCATENATE?
CONCATENATE is the older function available since Excel 2007. CONCAT was introduced in Excel 2016 as a replacement. The main difference is that CONCAT can accept a range of cells (e.g., A1:A5) as a single argument, while CONCATENATE requires each cell to be listed separately. CONCAT is also slightly shorter to type.
Can I use CONCAT to add a separator like a comma or space?
Yes, but you must add the separator as a separate argument. For example: =CONCAT(A2, ", ", B2). If you want to join a whole range with separators, use TEXTJOIN instead, which has a dedicated delimiter argument.
Does CONCAT ignore blank cells?
No. CONCAT includes empty cells as empty strings, so you might get double spaces or missing separators. To skip blanks, use TEXTJOIN with the ignore_empty argument set to TRUE, or use a more complex formula with IF.
How do I combine text and a formula result?
Simply include the formula as an argument. For example: =CONCATENATE("Total: ", SUM(A1:A10)). The result of the SUM formula is converted to text and appended.
Can I use CONCAT with numbers without losing formatting?
Yes, but numbers may lose their formatting (e.g., currency symbols, decimal places). To preserve formatting, use the TEXT function: =CONCATENATE("Price: ", TEXT(A2, "$#,##0.00")).
Why does my CONCAT formula show a #NAME? error?
This usually means the function is not recognized. If you’re using Excel 2013 or earlier, CONCAT doesn’t exist; use CONCATENATE instead. Also check for typos and ensure the function name is not in quotes.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- MacBook Charger Not Working? Fix It Now
- Fix Windows Cannot Be Installed to This Disk
- Zoom on iPhone & iPad: Host or Join a Meeting
- Fix iPhone SE 4 Unable to Activate: Step-by-Step
- Keep Important Emails From Getting Buried in iPhone Mail Categories
- How to Clear RAM on iPhone: Speed It Up