Forum Discussion

Brass Contributor

Jul 07, 2022

Solved

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

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

excel

Formulas and Functions

Riny_van_Eekelen
Jul 07, 2022
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)

Riny_van_Eekelen

Platinum Contributor

Jul 07, 2022

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)