Jump to content


Photo

Concatenating line breaks in Excel

concatenate CHAR wrap text

  • Please log in to reply
1 reply to this topic

#1 jegllaume

jegllaume

    Newbie

  • Members
  • Pip
  • 6 posts

Posted 07 September 2015 - 12:42 PM

Hello there, 

 

I would like to know if there is any way to add a return text function within one excel cell while using the concatenate key (&)...

 

For example A1= jean, A2=pierre and

A3=A1&A2 (adding something, i don't know, so that wihtin the same cell "jean' appears in one line and "pierre" in the next line)

A3= jean

       pierre 

 

Please, note, I am talking about the wrap function...

 

pleased to hear,

thanks in advance,

 

jegllaume



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 07 September 2015 - 01:27 PM

Hi jegllaume,

 

Yes, you can do this in one of two ways. The first is with a formula like this:

 

=A1&"
"&B1

 

Note that you will need to press Alt + Enter to input a line break in a formula.

 

This is the same as concatenating any other text, but with a line break between the quote marks.

 

You can find an in-depth explanation of concatenating in the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

A different way to do the same thing is with a formula like this:

 

=A2&CHAR(10)&B2

 

In Excel, every possible character has a code number and the code number for a line break is 10. By using the CHAR function, you can easily concatenate the line break character and create a much less confusing formula.

 

A very important note is that you must enable the Wrap Text option in order for line breaks to be displayed in cells. You can see how to enable Wrap Text in the video lesson: Essential Skills Lesson 4-6 Merge cells, wrap text and expand or collapse the formula bar.

 

I have attached a workbook showing both of these formulas in action:

 

Attached File  ConcatenateLineBreaks.xlsx   9.67KB   184 downloads

ConcatenateLineBreaks.png

 

I hope you're now able to create the formula you need, but please feel free to reply if you're still having any trouble with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users