How to add new line break to a cell formula in Excel

I have MS Excel looks like this.

A            B          C
---------------------------------
Name         Age        Gender
---------------------------------
Tom Hanks    67         Male
---------------------------------
Jennifer Ann 58         Female
---------------------------------
Mark T       53         Male

I want to create a formula in column D

that gives me the result in this format.

D
---------------------------------
Info
---------------------------------
Tom Hanks
67
Male
---------------------------------
Jennifer Ann
58
Female
---------------------------------
Mark T
53
Male

I tried this formula but did not work

=A2 & "\n\r" & B2  & "\n\r" & C2

but what I got was

Tom Hanks\n\r67\n\rMale

how to do that?

>Solution :

You may use any one of the following depending on your excel version:

• Formula used in cell D2 –> works with Excel 2019+ onwards.

=TEXTJOIN(CHAR(10),,A2:C2)

Or,

• Formula used in cell E2 –> believe it works across all versions

=A2&CHAR(10)&B2&CHAR(10)&C2

Or,

• Formula used in cell F2 –> Works with MS365 only

=MAP(A2:A4,B2:B4,C2:C4,LAMBDA(x,y,z,TEXTJOIN(CHAR(10),,x,y,z)))

Note: In all the above formulas you need to hit the wrap feature available under Home Ribbon in Alignment Group.