LinkBack URL
About LinkBacks
I have attached a sample file where I want to concatenate data in colums A2:H2 into one column with line breaks. But CHAR(10) function alonwith CONCATENATE is not working. I have formatted the destination cell for wrap text. Please help.
I have attached a sample file where I want to concatenate data in colums A2:H2 into one column with line breaks. But CHAR(10) function alonwith CONCATENATE is not working. I have formatted the destination cell for wrap text. Please help.
Last edited by saigalp; 08-09-2012 at 07:11 AM.
You need to use CHAR(10) and CHAR(13):
=A2 & CHAR(10) & CHAR(13) & B2 & CHAR(10) & CHAR(13) & C2 & CHAR(10) & CHAR(13) & D2 & CHAR(10) & CHAR(13) & E2 & CHAR(10) & CHAR(13) & F2 & CHAR(10) & CHAR(13) & G2 & CHAR(10) & CHAR(13) & H2
Sorry, Andrew-R, but this also does not work. I need to have the data in the form of a address label in one colummn. Concatenate is working but line break function is not working.
Last edited by saigalp; 08-09-2012 at 08:19 AM.
how about???
=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2,Char(10),D2,char(10),E2,char(10),F2,char(10),G2)
wrap the cell then adjust the width of the column, center justified,center align.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
if that doesn't work try this one.
say =CONCATENATE(A3,"@",B3,"@",C3)
instead of the @ sign press alt+enter so the formula will become like below
=CONCATENATE(A3,"
",B3,"
",C3)
alt+enter will force the breaks.
so oo...
then if this is what you want just fill down the column.
I see no reason why you can't get CHAR(10) to work when the cell is formatted to wrap unless your column width is less than the width of the longest cell to be concatenated.
Taking the above solutions a step further this will discount blank cells.
Formula:
Please Login or Register to view this content.
Last edited by Marcol; 08-09-2012 at 09:27 AM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Thanks for the solution. Works perfectly.
---------- Post added at 06:55 PM ---------- Previous post was at 06:48 PM ----------
Thanks for the reply.
This might save you a little typing.
Drag the formula in A7 down, and in B7 across then down.
The result is in column H
O.K. Thanks.
many thanks vlady
wrap works with me... Shift+R improves the quality ** this image. CTRL+F5 reloads the whole page.
Im using Excel 14.7.7 and CHAR(10) function is not working to put cells on different lines. I tried wrap text. Please help
@ baby95120
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
I struggled for quite a while and none of this seemed to work for me at all until I discovered the following:
Unless the cell's format has been set to word wrap, Excel will not display the contents of the cell with its newlines but just as one single line of text without newlines.
As soon as I changed the format to word wrap suddenly all the newlines I'd inserted with CHAR(10) etc. . appeared.
What make this less obvious to spot is that if you enter text into a field with newlines (using Alt-Enter), Excel automatically sets the format of the cell to word wrap.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks