How do I separate texts in a cell that are separated with a comma into individual lines with line breaks, still in one cell

[uysal5]

Hi,

I have used a formula (TEXTJOIN) to put together multiple rows into one cell by looking up the same email address so they are grouped as one text, but I need them to be listed individually with line breaks (see example table below).

Any help is appreciated.

Name	Group clients	Email address	Column1	Column3
Tom	ABC Ltd	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Minie Mouse Mr Micky Mouse
Jerry	XYZ Ltd	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Minie Mouse Mr Micky Mouse
Minnie	Ms Minie Mouse	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Minie Mouse Mr Micky Mouse
Micky	Mr Micky Mouse	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Minie Mouse Mr Micky Mouse

Many thanks
Figen

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what is the formula you are using?

 

[JvdV]

So instead of using a ', ' as the list seperator, use CHAR(10) in the 1st paramter of your TEXTJOIN() function and tick textwrap on column 3.

 

[uysal5]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what is the formula you are using?

Will do.
I am using Office 365 business

 

[JamesCanale]

Use Char(10). You still have to select "Wrap Text".

MrExcelPlayground13.xlsx
	A	B	C	D	E
1	Name	Group clients	Email address	Column1	Column 3
2	Tom	ABC Ltd	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Ms Minie Mouse Mr Micky Mouse
3	Jerry	XYZ Ltd	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Ms Minie Mouse Mr Micky Mouse
4	Minnie	Ms Minie Mouse	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Ms Minie Mouse Mr Micky Mouse
5	Micky	Mr Micky Mouse	tomandjerry@hotmail.com	ABC Ltd, XYZ Ltd, Ms Minie Mouse, Mr Micky Mouse	ABC Ltd XYZ Ltd Ms Minie Mouse Mr Micky Mouse
Sheet16

Cell Formulas
Range	Formula
D2:D5	D2	=TEXTJOIN(", ",TRUE,$B$2:$B$5)
E2:E5	E2	=TEXTJOIN(CHAR(10),TRUE,$B$2:$B$5)

 

[uysal5]

So instead of using a ', ' as the list seperator, use CHAR(10) in the 1st paramter of your TEXTJOIN() function and tick textwrap on column 3.

Perfect! Solved!
Thank you so much