NAME? error: Usually means you‘re missing an opening or closing quotation mark around a text string. Double check that all explicit text is fully enclosed in "quotes".
Have you ever needed to combine text from multiple cells into one cell in Microsoft Excel? Maybe you have a spreadsheet with first and last names in separate columns, but you need them together in a single column. Or perhaps you want to build a complete address from separate street, city, and state values.
Whenever you need to join text from different sources, the process is called concatenation. Excel provides several useful functions to make concatenating strings easy. In this in-depth guide, we‘ll explore all the ways you can concat cells and columns in Excel.
Why Concatenate in Excel?
First, let‘s consider why you might need to concatenate text in a spreadsheet. Concatenation allows you to:
- Combine first and last names into full names
- Join multiple columns like street, city, state into a full mailing address
- Build sentences or paragraphs from separate fragments
- Generate unique keys or IDs by combining multiple values
- Automate repetitive combinations of text and values
Any time you find yourself manually copying and pasting text together in Excel, concatenation functions can help automate the process, saving you time and reducing errors. Even if your data arrives in a less-than-ideal format, concatenation gives you the power to combine values and reshape it any way you need.
Now let‘s dive into the core Excel functions for concatenating strings, starting with the most basic and progressing to the most advanced and flexible.
The CONCATENATE Function
The CONCATENATE function is the oldest and most widely supported method for joining text strings in Excel. You‘ll find it in every version of Excel dating back to Excel 2000.
The syntax for CONCATENATE is straightforward:
=CONCATENATE(text1, [text2], ...)
Within the parentheses, you simply list out each piece of text you want to join together. These can be cell references, quoted text strings, or a mix of both. For example:
=CONCATENATE(A1, " ", B1)
In this example, the formula joins the value from cell A1, a space character in quotes " ", and the value from cell B1 into a single text string.
The CONCATENATE function accepts up to 255 arguments. But listing out hundreds of arguments gets cumbersome fast. Plus, inserting a delimiter like a space between each text value quickly inflates your formulas.
Fortunately, Excel offers more concise concatenation methods that require less typing, as we‘ll see next.
The CONCAT Function
Introduced in Excel 2016, the CONCAT function is an improved, simplified version of CONCATENATE. It has an identical syntax:
=CONCAT(text1, [text2], ...)
However, CONCAT removes the 255 argument limit. You can feed in as many text values as you want. More importantly, it accepts ranges of cells, something CONCATENATE cannot do.
For instance, to combine cells A1 through A10 with spaces in between, you can use:
=CONCAT(A1, " ", A2, " ", A3, " ", A4, " ", A5, " ", A6, " ", A7, " ", A8, " ", A9, " ", A10)
Or more simply:
=CONCAT(A1:A10, " ")
CONCAT treats the second argument as the delimiter to insert between each value in the range A1:10. This is much cleaner and easier to read.
The CONCAT function is a good choice if you‘re concatenating a large number of values or working with a range. But there‘s an even simpler option for quick concatenation jobs.
The "&" Ampersand Operator
Want the easiest, fastest way to join text in Excel? Skip the functions altogether and use the & ampersand operator. Excel interprets the & as an instruction to concatenate the values on either side.
For example, to combine the values from A1 and B1 with a space in between:
=A1 & " " & B1
No Function needed. The & offers a quick shorthand for smashing text together. You can chain together as many &s as needed:
=A1 & B1 & C1 & D1
The main limitation of the & operator is that it doesn‘t allow ranges like A1:A10. You have to list out each cell reference individually. But for joining a small number of values, the & is the most no-nonsense option.
The TEXTJOIN Function
Looking for the ultimate in concatenation flexibility and power? Meet TEXTJOIN.
Available starting with Excel 2019 and Microsoft 365, TEXTJOIN offers all the benefits of the other concatenation functions, plus some handy extras:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
The first argument "delimiter" is the text you want to insert between each concatenated value. This saves you from having to type out the delimiter over and over.
The second argument "ignore_empty" is a Boolean that tells the function whether to skip over empty cells. This is super useful when concatenating ranges that may contain blank cells.
Finally you have the standard list of text values to concatenate. But again, these can be individual cells or ranges.
Putting it all together:
=TEXTJOIN(" ", TRUE, A1:A10)
This formula joins the values in A1 through A10, inserts a space character between each, and ignores any empty cells.
With TEXTJOIN, you get an unmatched degree of control over how your text values get combined. The trade-off is that it‘s only available in the latest versions of Excel. If you‘re running an older version, you‘ll need to stick with CONCAT or &.
Concatenating with Line Breaks
By default, concatenated text strings appear on a single line within a cell. But sometimes you may want to insert line breaks between the combined values.
To add a line break in Excel, you use the CHAR function with the ASCII code for the line break character:
- Windows: CHAR(10)
- Mac: CHAR(13)
For example:
=A1 & CHAR(10) & B1
This formula joins the text from A1 and B1 with a line break in between on Windows. On Mac, you‘d use CHAR(13) instead.
After adding a line break, you‘ll also want to enable text wrapping on the cell to make the line break visible. Select the cell, go to the Home tab, and click "Wrap Text" in the Alignment group.
With this formatting applied, your concatenated text will display on multiple lines.
Concatenating Columns and Ranges
Need to concatenate two or more entire columns? Excel makes it easy with AutoFill.
- In an empty column, type out the concatenation formula for the first row. For example: =A1&" "&B1
- Click and drag the small square "fill handle" at the bottom-right of the cell down the column.
Excel automatically copies the formula down, adjusting the cell references for each row. Instant concatenated column!
If you need to concatenate a two dimensional range, you can use the TRANSPOSE function to essentially flatten the range into a single column first:
- In an empty column, type =TRANSPOSE( and select your range of cells. Hit Enter.
- With the cell selected, press F9 to convert the TRANSPOSE to values.
- Delete the { } curly braces around the values.
- Type =CONCAT( just before the first value and a closing ) at the end.
This trick makes it possible to feed any grid of values into CONCAT at once.
Concatenation Troubleshooting
Even with the power of Excel functions on your side, concatenating can still go wrong in a few predictable ways:
-
Extra spaces: By default, Excel doesn‘t trim extra spaces from the start or end of text values. Use the TRIM function to remove any leading or trailing spaces before concatenating.
-
Numbers formatted as text: Concatenating a numeric value will convert it to text. To control the formatting, wrap the number in a TEXT function like =TEXT(A1,"$0.00") for currency.
-
Accidentally replacing entire columns: If you type a formula in a cell and press Ctrl+Enter, it will overwrite the entire column with that formula. Undo with Ctrl+Z and be sure to press Enter after typing a formula instead.
Advanced Concatenation Techniques
Once you‘ve got the basics of concatenation down, you can combine it with other Excel functions to accomplish all sorts of tricky text manipulations.
For example, what if you only want to concatenate cells that meet certain criteria? Wrap your concatenation formula inside an IF statement:
=IF(A1>0,CONCATENATE(A1,B1),"")
This only concatenates A1 and B1 if the value in A1 is greater than 0.
What about extracting a portion of the concatenated text? You can combine concatenation with Excel‘s text functions like LEFT, MID, and RIGHT:
=LEFT(A1&" "&B1,5)
This formula concatenates A1 and B1, then uses LEFT to extract just the first 5 characters from the result.
You can even use concatenation to build dynamic range references inside other formulas:
=SUM(INDIRECT("A" & ROW(A1) & ":B" & ROW(A1)))
Here, INDIRECT takes a string like "A1:B1" built by concatenating the row number, and converts it into an actual range reference that SUM can calculate.
The more you experiment with combining concatenation with other functions, the more powerful your formulas can become. Don‘t be afraid to let your creativity run wild!
Closing Text
I hope this guide has helped demystify concatenation in Excel. There truly is an Excel function for every type of concatenation problem, whether you just need to quickly smash two values together or you want surgical control over the results.
If you remember nothing else, keep the & ampersand operator at the front of your mind. Those three little characters can save you a ton of time over the "copy, space, paste, copy, space, paste" grind. And when & isn‘t enough, CONCAT and TEXTJOIN give you the power to concatenate like an Excel pro.
Now go forth into the world of spreadsheets and may you forever express your love of concatenation! Because when it comes to Excel, sometimes joining is better than tabling.