Pinterest Pixel

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

John Michaloudis
Ever faced a situation when you are combining text from multiple cells with line break as separator? 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.

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.

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

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(
3 Quick & Easy ways to Concatenate Excel With A Line Break

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)
3 Quick & Easy ways to Concatenate Excel With A Line Break

 

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)
3 Quick & Easy ways to Concatenate Excel With A Line Break

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

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

 

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

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

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
3 Quick & Easy ways to Concatenate Excel With A Line Break

 

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)
3 Quick & Easy ways to Concatenate Excel With A Line Break

 

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!

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

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.

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

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).

TEXTJOIN Formula in Excel

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!

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  Evaluate Formulas Step By Step in Excel

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...