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

Key Takeaways

• The CONCATENATE function, alongside CHAR(10), can be used to merge text from various cells in Excel with a line break separating each cell’s content.
• When using CONCATENATE to insert line breaks, it’s essential to apply the Wrap Text feature to the cell to correctly display the combined text on separate lines.
• Excel’s CONCATENATE and CHAR functions provide a straightforward way to compile data from multiple cells into one cell, maintaining readability with line breaks.

Looking for a new fix or hack for this? Keep reading!

CONCATENATE Excel Function

What does it do?

Joins two or more text strings into one string. The item can be a text value, number, or cell reference.

Formula breakdown:

=CONCATENATE(text1, [text2], [text3], …)

What it means:

=CONCATENATE(the first text, the second text, and so on…)

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.

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:

 download excel workbook Concatenate.xlsx

 

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)

 

Tips and Tricks for Concatenate Perfection

The Wrap Text Wizardry

Wrapping text in Excel is like tucking in the edges of your data so it all shows up nicely on screen. If you’ve ever felt puzzled by all your content jamming into one line even after concatenating, the solution lies in the “Wrap Text” feature. This little button on the Home tab under the Alignment group is your friend. By selecting your cells and clicking this button, your text magically wraps, making all lines visible. Sometimes, it’s all about adjusting the cell width too, to make sure nothing gets hidden unintentionally. Remember to select the cells, right-click, and select Format Cells or use the Ctrl + 1 shortcut to dive into the Alignment tab and tick the box next to “Wrap Text.” Dragging the fill handle (the small square at the bottom right of the selected cell) down will apply the same format to the rest. To see the results unfold, just go Home > Alignment > Wrap Text, and voila!

Avoiding Common Concatenation Pitfalls

Navigating concatenation can sometimes feel like tiptoeing through a minefield of potential glitches. One misstep and your data presentation could blow up into a mess! To avoid common pitfalls, pay close attention to details like extra spaces and punctuation. Remember, concatenating does not inherently add spaces between elements, so include them in your strings to keep words from colliding into each other. Also, make use of the CHAR function for line breaks—CHAR(10) on Windows or CHAR(13) on Mac—to insert a new line. Keep an eye out for leading or trailing spaces as these invisible stowaways can wreak havoc on data sort and search functions. Lastly, ensure that number formatting carries over correctly. Concatenating a cell with numbers might result in strings of text, so you may need TEXT function assistance to preserve or customize number formats.

Advanced Techniques for Power Users

Nested Functions with Concatenate

Combining the CONCATENATE function with other Excel functions unlocks even more power, allowing for dynamic and complex data manipulation. For example, when you nest the TEXT function within CONCATENATE, you can format dates and numbers in specific ways within your text string. It goes like this: =CONCATENATE("Sales as of ", TEXT(TODAY(),"mmmm d, yyyy"), ": $", TEXT(B2,"#,##0.00")). This beautifully merges formatted current date and currency within your text.

Thinking of a nested IF function with CONCATENATE? It’s a breeze once you get the hang of it. Imagine you want to concatenate a friendly reminder with a due status, like this: =CONCATENATE("Your payment is ", IF(C2="Yes", "received. Thank you!", "due. Please remit payment.")). Just like culinary layers in a fine lasagna, nesting functions with CONCATENATE can add layers of functionality to your dishes… I mean, data!

Crafting Dynamic Data Presentations

Leveraging CONCATENATE isn’t just about slapping data together – it’s about crafting a narrative that enhances understanding and engagement. To create dynamic data presentations within Excel, combine CONCATENATE with other functions like INDIRECT or VLOOKUP to pull in real-time data that updates automatically. This way, your data stories are not just static snapshots, but living canvases that change and grow.

For the data artists, Conditional Formatting can be layered with CONCATENATE to make key information pop. Imagine highlighting concatenated cells that contain certain keywords or exceed specific thresholds. The CONCATENATE function doesn’t need to operate in isolation; it’s a team player, meshing seamlessly with conditional logic, lookups, and dynamic references to create a masterpiece of clarity and insight.

FAQ: Mastering Excel’s Concatenate Function with Ease

How do I add a line break within an Excel cell using concatenate?

To add a line break inside an Excel cell using the CONCATENATE function, use the CHAR function with code 10. Here’s the formula: =CONCATENATE(text1, CHAR(10), text2, ...). Activate “Wrap Text” for the cell to display the line break correctly.

What are some common uses of concatenation with line breaks in Excel?

Common uses of concatenation with line breaks include creating multi-line address labels from separate columns, combining lists where each item appears on a new line, and formatting output for improved readability, such as in reports or dashboards.

How do you CONCATENATE with a separator in Excel?

In Excel, use the CONCATENATE function or the ‘&’ operator to merge cells with a separator like so: =CONCATENATE(A2, " - ", B2) or =A2 & " - " & B2. Replace ” – ” with any separator desired. For multiple cells with the same separator use the TEXTJOIN function: =TEXTJOIN(" - ", TRUE, A2:C2). The second argument specifies if empty cells are ignored (TRUE) or not (FALSE).

How do you code a line break in Excel?

In Excel, a line break is coded using the CHAR function with the number 10. In a formula, you’d use =CHAR(10). Remember to turn on “Wrap Text” in the cell format to make the line break appear.

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!