3 Quick; Easy ways to Concatenate Excel With A Line Break

3 Quick & Easy ways to Concatenate Excel With A Line Break

CONCATENATE Excel function joins two or more text strings into one string. The item can be a text value, number, or cell reference.

If you add a double quotation with a space in between ” ” then this will add a space between the texts selected on either side.

Line Break in Excel

Line Break in Excel is added to end the current line and start a new line within the same cell. A long text with a line break and without a line break is shown below.

In general, when you are typing a text in Excel and you need to add a line break, you can simply press Alt + Enter and Excel will take you to the new line within the same cell.

But what if you want that line break when you are trying to combine texts from different cells using a formula.

This is exactly what we will cover in the next section!

3 ways to Excel Concatenate with Line Break

Want to add a line break in between each text string?

This is done by entering the CHAR( ) function in between each text string/argument.

CHAR function returns a character specified by a number from 1 to 255. For example, CHAR(10) returns a line break in windows, CHAR(34) returns a ” (double quote), CHAR(64) returns @, etc.

So, by simply adding CHAR(10) in between your formula you can enter a line break. You will need to select WRAP TEXT in order to see each text on a separate line.

Watch the video about Excel Concatenate Line Break on YouTube and give it a thumbs-up!

Follow the step-by-step tutorial below to see how easy it is to add a line break in Concatenate Excel using employee data on the example below. Don’t forget to download the Excel workbook and follow along:

METHOD 1: Use Concatenate Excel Formula

STEP 1: We need to enter the CONCATENATE Excel function in a blank cell:

=CONCATENATE(

STEP 2: The CONCATENATE arguments:

text1, [text2], [text3], …

Which text do you want to join together?

Let us select all the columns:

=CONCATENATE( A12, B12, C12, D12)

Now let’s add the function CHAR(10) to add a line break between each text

=CONCATENATE(A12, CHAR(10), B12, CHAR(10), C12, CHAR(10), D12)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

STEP 3: Go to Home > Alignment > Wrap Text to show the text in multiple lines and you now have all of the results!

This is how you can add a new line in excel concatenate formula. Let’s look at the other two methods as well!

METHOD 2: Use & (Ampersand) Sign

Instead of joining text using the Concatenate function, you can use the Ampersand (&) sign. The & sign works as a concatenation operator and clues together text for different cells as well as the char function.

The formula to combine text with line break in the previous example with & sign will be:

=A9 & CHAR(10) & B9 & CHAR(10) & C9 & CHAR(10) & D9

METHOD 3: Use TEXTJOIN formula

This function is available in Excel 2019 & Office 365 only.

Instead of selecting individuals cells and adding a CHAR function again and again in the formula, you can use the advanced versions of concatenate function – TEXTJOIN.

TEXTJOIN can be used to concatenate a range of cells using a delimiter.

Excel requires three arguments for TEXTJOIN function:

=TEXTJOIN( delimiter , ignore_empty , text1, [text2], [text3]. )

1. Delimiter – The character you need to insert in between each text.
2. Ignore_empty – It should be TRUE if you want to ignore empty cells and FALSE if you want to include the empty cells.
   By default, the value will be TRUE.
3. Text – Range of cells you want to combine.

So, TEXTJOIN function in our example will look like this:

=TEXTJOIN(CHAR(10), TRUE , A10:D10)

Conclusion

In this article, you have gone through the 3 quick ways to combine text from different cells with a line break. You can use either a concatenate function or a & sign or TEXTJOIN function.

Make sure to apply Wrap Text to the cell when you are using Excel Concatenate new line!

How to Concatenate in Excel

The CONCATENATE Excel function is used to join several text strings into one string. It is often the solution when text which we would like to see appearing together is located in different places, or across several cells.

Starting with Excel 2016, the CONCATENATE function is being phased out in favor of the CONCAT Excel and TEXTJOIN Excel functions. For the time being, CONCATENATE is still available for backward compatibility.

We will explore how all three functions work, so that you will be able to decide when it’s appropriate to use each one.

Download your free CONCATENATE Excel practice file!

Use this free CONCATENATE Excel file to practice along with the tutorial.

How to concatenate in Excel

CONCATENATE

The syntax of the CONCATENATE function is:

Each argument may be a cell reference or a text string typed directly into the formula. Only one argument is required, but if you’re using this function, it’s likely that you have at least two. CONCATENATE can accept up to 255 arguments, with a maximum of 8,192 characters.

How to use CONCATENATE

To learn how to use this function, let’s take a look at an example. In the worksheet below, the address elements are all split across five columns.

We can use CONCATENATE to join all five elements into one string. It is important to remember that if a space or some other character (e.g., comma) is required between elements, it has to be manually typed between each cell reference within double quotation marks.

Note that since both a comma and space were required after B2 and C2, the text string “, ” was typed as an argument after both cell references.

Concatenate with numeric values

When submitting numeric values directly into a CONCATENATE formula, there is no need to enter the numeric values within quotation marks.

In the following example, the street number will be entered directly into the CONCATENATE formula and will be joined to the cell references to create the full address.

When numeric values are entered as a string, they are interpreted as numbers. Therefore, typing a space within that argument will have no effect on the value which is returned. If any other delimiter is entered, Excel will return an error message.

In our example above, we entered a space between two double quotes as our second argument, and this was reflected in the output cell.

If a numeric value is entered as a string within double quotes, then it is converted to text and a delimiter may be placed within that argument since it behaves like any other text value.

Concatenate strings with line breaks

We may want to display some of these elements in separate lines. To do so, we can use the CHAR(10) unicode character to insert a line break.

Note that in the above instance, we removed the space and comma after B2 since it was no longer needed with the insertion of the line break character — CHAR(10).

Column F was also formatted to Wrap Text so as to display the results.

Limitations of CONCATENATE

1. CONCATENATE can get a bit cumbersome, especially when adding delimiters. A somewhat common unexpected is that a quotation mark appears in the result string because of omitting a comma between arguments. For instance:

will return a result of Good "morning because no comma was entered to separate the two strings.

1. As useful as the CONCATENATE Excel function is, if the strings to be joined are in a contiguous range, and require no delimiters like commas or spaces, it is unable to accept a range as an argument.

The following example has elements of a group of phone numbers broken out across four columns.

If we attempt to apply the CONCATENATE Excel function using the range A2:D2 as an argument, Excel will return a #VALUE! error.

This is too bad, but this is where the CONCAT function steps up.

CONCAT

Starting with the 2016 version of Excel, the CONCAT Excel function has joined the family of text functions, and like CONCATENATE, CONCAT exists to join multiple text strings into one string.

Each argument may be a cell reference, a range of cell references, or a text string typed directly into the formula. With this flexibility, we can quickly combine the above phone numbers into one string by using the A2 to D2 range as the argument of the CONCAT function, which CONCATENATE cannot do.

CONCAT maintains all the other features of CONCATENATE, including the need to manually insert individual delimiters such as spaces, ampersands, commas, etc., between strings if needed.

For example, to place the area code within dashes in the above example, we would enter:

Limitations of CONCATENATE and CONCAT

A major disadvantage of both the CONCAT and CONCATENATE functions is that delimiters have to be entered individually, even if the delimiter between each argument is the same. This can be very time-consuming, difficult to read, and is subject to errors.

TEXTJOIN

My personal favorite of the three string-joining functions is TEXTJOIN. TEXTJOIN was rolled out with Excel 2019 and is a real time-saver. It has three required arguments.

Argument definitions

• delimiter (required) is a character or characters which will separate each argument, entered within double quotes.
• ignore_empty (required) is a setting that tells Excel what to do with empty cells. If TRUE, empty cells are ignored. If FALSE, a space is displayed to represent the empty cell in the result.
• text1 (required) is a text string, or array of strings.
• [text2] is an optional text string or array of strings. A total of 252 text arguments are permitted, with a maximum of 32,767 characters.

The most obvious advantage of TEXTJOIN is that if the delimiters between strings are identical, it eliminates having to type the same delimiter over and over.

Therefore it is quite possible that only three arguments will be required, even where the desired output is stretched across several strings. This is because the delimiter was entered once, at the beginning of the formula, and the third argument (text1) can be a range.

In the following example, each customer’s title (if any), first name, last name, and the elements of their address are to be joined — each separated by a space. We also want to insert a line break after the name, so the CHAR(10) unicode character is entered after the reference to cell C2.

Notice that the second argument is set to TRUE so that if Excel comes across any empty cells, it does not create an extra space in the output cell. Column I has been set to wrap text so that the result of the line break is displayed.

In order to get a similar result from the CONCATENATE function, the entry would have been:

With the CONCAT Excel function, the entry would be:

Though ranges are permitted with the CONCAT function, it provides no advantage here since delimiters are required between each string. That would mean a total of 15 arguments using the older functions, compared with five arguments in TEXTJOIN.

Furthermore, CONCATENATE and CONCAT cannot ignore empty cells so an extra delimiter is created for each occurrence of blank cells, for example, with the omission of the title or some portions of the address in the examples above.

The ignore_empty argument in TEXTJOIN handles this perfectly so that unnecessary delimiters are removed from the results.

Concatenation operator (&)

It is possible to concatenate in Excel without the use of functions. This is done with the use of the ampersand (&) symbol as a concatenation operator.

Let’s compare using the concatenation operator with using the CONCAT function. We’ve chosen CONCAT because it is the newer version of CONCATENATE, but the same principle would have applied to CONCATENATE.

The format here is that the & operator is entered after each cell reference and value, including the space character supplied within double quotes. The results in column D are identical to column E’s use of CONCAT, shown below.

Of course, the concatenation operator doesn’t accept cell ranges, and the disadvantage of empty cells is only solved by using the TEXTJOIN Excel function.

TEXTJOIN allows you to give a more uniformed finish in the output cells, even if some elements are missing.

Learn more

Have you tried using all four methods to concatenate? Which one do you prefer? Tell us in the comments and stay in the know with our Excel courses and other resources.

Start your journey with the free Excel in an Hour course today!

Free Excel crash course

Learn Excel essentials fast with this FREE course. Get your certificate today!

Start free course

Claudia is a content writer and course instructor at GoSkills. If she’s not at work, she’s probably tackling a genealogy project.

Concatenate Excel Function

The CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers. For example, the formula “=CONCATENATE(A4,“ ”,B4)” joins the cells A4 and B4 containing the values “Rock” and “Band” respectively. It returns the string of cell A4, followed by a space character, and the string of cell B4. Hence, the output is “Rock Band.”

With CONCATENATE, the content of different cells is combined together and placed in a single cell. The CONCATENATE function in Excel acts as a substitute for the “&” operator and joins the values. The usage of the “&” operator makes the formula’s complex. In contrast, the CONCATENATE formulas are simple and easy to understand.

Syntax

The syntax of the function is shown in the following image:

The function accepts the following arguments:

• Text1: This is the first cell reference, text value or number to be joined.
• Text2: Thisis the second cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more , text value or number to be joined.

“Text1” is the required argument. “Text2” and the subsequent arguments are optional and can be added based on the user’s requirement.

Note: The optional arguments are enclosed within the square brackets.

How to Use the CONCATENATE Function in Excel?

Let us understand the working of the CONCATENATE function with the help of the following examples.

Example #1

The following table shows the first and the last names in columns A and B, respectively. We want to join the first name and the last name together so that the full name is displayed in the column C. Use the CONCATENATE function of Excel.

To introduce a space between the first and the last names, let us pass another argument to the CONCATENATE formula (between the text values A2 and B2).

The second argument is a string literal with a space enclosed within the double quotes (“ ”).In a string literal, characters exist at their literal value.

We apply the following formula in cell C2, as shown in the succeeding image.

The output is shown in the following image. A space has been inserted between the first and last names in cell C2.

Example #2

The following image shows three tables titled “table 1,” “table 2,” and “table 3.” All the three tables consist of the names of ten employees of an organization. In addition, “table 1” and “table 2” show the employee IDs and the native places (cities), respectively.

The names of “table 2” and “table 3” are not according to the sequence given in “table 1.”

For every employee, we want to join the IDs and the cities with a hyphen in between. The resulting concatenated values should be listed in the second column of “table 3” which displays question marks.

Since “table 2” and “table 3” do not show the names in the order listed in “table 1,” we cannot pass the reference values directly. Moreover, passing direct values is a time-consuming task which requires the matching of entries.

Instead, let us use the VLOOKUP function to look up the employee ID and the city. The values thus returned can be concatenated.

We apply the following formula to cell I2. The VLOOKUP function is nested within the CONCATENATE excel function.

The output of the formula is shown in cell I2 of the following image.

Drag the formula to the remaining cells of column I, as shown in the following image.

The final output is displayed in column I of “table 3.”

The “#NAME” Error in CONCATENATE Excel Function

When the user passes an argument apart from the reference value in the formula, it should be enclosed within double quotes. The arguments within the double quotes are the string literals written in the C++ programming language. String literals are used in MS Excel and other office packages.

If the string is not enclosed within double quotes, the CONCATENATE excel function does not recognize it. Hence, it returns the “#NAME” error.

The succeeding image shows the error returned by the CONCATENATE formula in cell C2. This takes place because the second string argument is not enclosed in double quotes.

In the CONCATENATE function, the result of formulas can be concatenated. For this, the different formulas must be passed as an argument to the function.

Limitation of the CONCATENATE Excel Function

With the CONCATENATE excel function, we can combine all the text values of a list into a single string. The function accepts the text values as arguments.

The limitation of the CONCATENATE function is that we cannot pass a range of values as an argument. As a result, the user has to enter individual cell references one by one in the function. This is a time-taking and challenging task for the user.

If we pass a range of values, the CONCATENATE function picks up the cell values of the row to which the formula has been applied. The same is displayed in the following image.

To overcome this limitation, a new function, TEXTJOIN has been introduced in the latest version of the Excel. It concatenates the values on specifying the range (like A2:A14), rather than individual cell references.

The syntax of the TEXTJOIN function is stated as follows:

“=TEXTJOIN(delimiter, ignore_empty, range)”

• Delimiter: It refers to the separators like “tab,” “semicolon,” “comma,” and “space” used between the values to be combined.
• Ignore_empty: It checks if the empty cells are to be ignored or added to the result. If it returns “true,” the empty cells are ignored. If it returns “false,” the empty cells are included in the result.
• Range: It is the range of values to be concatenated.

All the three arguments “delimiter”, “ignore_empty,” and “range” are mandatory.

Frequently Asked Questions (FAQs)

The CONCATENATE excel function allows users to combine the text values of different cells and place them in a single cell.
The syntax of the CONCATENATE formula is stated as follows:
“=CONCATENATE(text1,[text2], …)”

Where,
• “Text1” refers to the first value to be joined. This value can be a cell reference, text value or number.
• “Text2” refers to the second value to be joined. This value can also be a cell reference, text value or number.

“Text1” is a required argument, while the subsequent arguments are optional.

For example, the following formula combines the text values of cells A2 and B2: “=CONCATENATE(A2, B2)”

The steps to concatenate strings containing a comma are listed as follows:

1. Select a blank cell in which you want the output of the CONCATENATE excel function.
2. Enter the following formula in the blank cell.
“ =CONCATENATE(C1,“,”,E1)”
Where,
• C1,E1 – It refers to the cells containing the values to be joined.
• “,” – It refers to the separator or the delimiter that separates values. It is always enclosed within double quotes.
3. Press the “Enter” key. The output consisting of the concatenated string appears in the blank cell.

The steps to concatenate text and dates are listed as follows:

1. Select a cell in which you want the output of the CONCATENATE formula.
2. Enter the following formula in the selected cell:
“ =CONCATENATE(D2, “ ”,TEXT(E2, “mm/dd/yyyy”)”
Where,
• D2 is the cell that contains the text value to be concatenated.
• E2 is a cell that contains the date. It is to be combined with the cell D2.
• mm/dd/yyyy refers to the specific date format.
3. Press the “Enter” key.
The output shows the concatenated text and date in a single cell. The date is displayed in the desired format.

Concatenate Excel Function Video

Recommended Articles

This has been a guide to CONCATENATE function in Excel. Here we discuss how to use CONCATENATE in Excel and its formula, along with examples and downloadable templates. You may also look at these useful functions in Excel–