Splitting information from different columns to a single cell in different lines.

[Rudrabhadra]

I am having a master database in which the data is arranged in different columns. As per requirement I need to make the required information from the master database and put it in one cell with different lines (Alt+Enter) in another worksheet. As the serial number is unique I had used the lookup and concatenate functions for getting the requried data. I am stuck up in sepearating it into differnt lines in the same cell. I understand that (alt+enter) seperates to the next line, but not sure how to do it while using formulas.
Could anyone hlep me please..

[#2 · Accepted solution]

@Rudrabhadra Use CHAR(10) every time you want to enter a line break and don't forget to set the cells to wrap text.

If you use CONCAT or CONCATENATE, that may be quite some typing as you have to include CHAR(10) between all text arguments. To make it  bit easier, you could type the formula =CHA(10) in a cell. Give it a name, e.g. lb (small L, small B) and then use that. Something like =CONCAT(D1,lb,E1,lb,F1,lb, etc...)

Or if you have MS365 or Excel 2021, use TEXTJOIN and use CHAR(10) as the delimiter. that could look like this:

=TEXTJOIN(CHAR(10),,D1:L1)