TLDR; Excel concatenation is a powerful tool for combining different text, number, or date values into a single string. The basic syntax of concatenation in Excel is to use the “&” operator to join two or more strings or values. We can also use the CONCATENATE function or the new CONCAT function. To concatenate numbers, we can use the “&” operator or CONCATENATE function, and to add delimiters, we can simply include them in our formula. We can also use the TEXT function to format numbers or dates before concatenating them. If you want to concatenate cells from different sheets or with line breaks, you can use sheet names or CHAR(10) function.
Basic syntax of Excel concatenation
The basic syntax of concatenation in Excel is to use the “&” operator to join two or more strings or values. For example, to concatenate the first name and last name from two different cells, we can use the following formula:
=A2 & " " & B2
This will combine the values in cells A2 and B2, separated by a space.
Concatenating Text
When we concatenate text in Excel, we are usually trying to create more readable or descriptive labels, titles, or sentences.
Joining text strings with the “&” operator
The simplest way to concatenate text in Excel is to use the “&” operator to join two or more text strings. For example:
="Hello" & " " & "world"
This will create a single text string that reads “Hello world”.
Concatenating text with the CONCATENATE function
The CONCATENATE function is another way to concatenate text in Excel. It works by taking a series of text strings as arguments and joining them together. For example:
=CONCATENATE("Hello", " ", "world")
This will produce the same output as the previous example.
Using the CONCAT function
Starting with Excel 2019, there is a new CONCAT function that can be used instead of CONCATENATE. The CONCAT function works in a similar way, but allows us to skip empty cells in our concatenation. For example:
=CONCAT(A1:A4)
This will concatenate all the text values in cells A1 through A4, skipping any that are blank.
Concatenating Numbers
When we concatenate numbers in Excel, we are usually trying to create unique identifiers, reference numbers, or codes.
Combining numbers with text
To combine a number with text in Excel, we can use the “&” operator or the CONCATENATE function in the same way as with text strings. For example:
="Order #" & A1
This will produce a text string that reads “Order #123”, assuming that the value in cell A1 is 123.
Joining numbers with the “&” operator
We can also join multiple numbers together using the “&” operator. For example:
=A1 & B1 & C1
This will concatenate the values in cells A1, B1, and C1 into a single string, with no separator.
Concatenating numbers with the CONCATENATE function
We can use the CONCATENATE function to join multiple numbers together, using the same syntax as with text strings. For example:
=CONCATENATE(A1, B1, C1)
This will produce the same output as the previous example.
Concatenating Dates
When we concatenate dates in Excel, we are usually trying to create custom date formats or to join dates with text or other values.
Combining dates with text
To combine a date with text in Excel, we can use the “&” operator or the CONCATENATE function, just like with numbers or text strings. For example:
="Report for " & TEXT(A1, "mmm d, yyyy")
This will produce a text string that reads “Report for Feb 14, 2023”, assuming that the value in cell A1 is a date.
Joining dates with the “&” operator
We can also join multiple dates together using the “&” operator. For example:
=A1 & B1 & C1
This will concatenate the dates in cells A1, B1, and C1 into a single string, with no separator.
Concatenating dates with the CONCATENATE function
We can use the CONCATENATE function to join multiple dates together, using the same syntax as with text strings or numbers. For example:
=CONCATENATE(A1, B1, C1)
This will produce the same output as the previous example.
Concatenating with Delimiters
When we concatenate values in Excel, it is often useful to include separators or delimiters between the different values.
Adding delimiters to concatenated strings
To add a delimiter between concatenated strings, we can simply include the delimiter in our formula. For example:
=A1 & "-" & B1 & "-" & C1
This will concatenate the values in cells A1, B1, and C1, separated by hyphens.
Using the TEXT function to format numbers and dates
We can use the TEXT function to format numbers or dates before concatenating them. For example:
=TEXT(A1, "$#,##0.00") & " - " & TEXT(B1, "mm/dd/yyyy")
This will produce a text string that reads something like “$1,234.56 – 02/14/2023”, depending on the values in cells A1 and B1.
Frequently Asked Questions
What is the difference between “&” and CONCATENATE?
Both the “&” operator and the CONCATENATE function can be used to concatenate values in Excel. However, the “&” operator is simpler and more commonly used, while the CONCATENATE function offers more flexibility and can be easier to read for longer formulas.
How do I add a space between concatenated text strings?
To add a space between concatenated text strings, you can simply include a space in your formula. For example:
=A1 & " " & B1
This will concatenate the values in cells A1 and B1, separated by a space.
Can I concatenate cells from different sheets?
Yes, you can concatenate cells from different sheets in Excel by including the sheet name in your formula. For example:
='Sheet1'!A1 & 'Sheet2'!B1
This will concatenate the value in cell A1 from Sheet1 with the value in cell B1 from Sheet2.
How do I concatenate cells with line breaks?
To concatenate cells with line breaks, you can use the CHAR(10) function to insert a line break. For example:
=A1 & CHAR(10) & B1
This will concatenate the values in cells A1 and B1, separated by a line break.
