How to Add a New Line with the CONCATENATE Formula in Excel

This is the sample dataset.

Method 1 – Using the CONCATENATE Formula to Add a New Line

The Combined column was added to the dataset.

Steps:

Enter the following formula in E4.

=CONCATENATE(B4,CHAR(10),C4,CHAR(10),D4)

Here, B4 is the Name, C4 is the Street Address, and D4 is the State. CHAR(10) will add a new line for each of the above and CONCATENATE will join them with line breaks.

Press ENTER and drag down the Fill Handle.

The combined strings will be displayed. To make line breaks visible, select Wrap Text and AutoFit Row Height.

Select the range of combined texts and go to the Home Tab >> Alignment Group >> Wrap Text.

Increase the row height to see the strings:

Select the range and go to the Home Tab >> Cells Group >> Format Dropdown >> AutoFit Row Height Option.

The combined text strings were added in new lines to the Combined column.

Read More: How to Enter within a Cell in Excel

Method 2 – Adding a New Line with the Ampersand Operator

This is the dataset.

Steps:

Enter the following formula in E4.

=B4&CHAR(10)&C4&CHAR(10)&D4

Here, B4 is the Name, C4 the Street Address, and D4 the State. CHAR(10) will add a new line for each and Ampersand (&) will join them with line breaks.

Press ENTER and drag down the Fill Handle.

The combined texts will be displayed.

To make the new lines visible, select Wrap Text and AutoFit Row Height.

The concatenated text strings will be displayed in new lines.

Read More: Excel: Inserts New Line in Cell Formula

Method 3 – Using the TEXTJOIN Function

This is the dataset.

Steps:

Enter the following formula in E4.

=TEXTJOIN(CHAR(10),TRUE,B4,C4,D4)

Here, B4 is the Name, C4 the Street Address, and D4 the State. CHAR(10) will add a new line for each and TEXTJOIN will join them with line breaks.

Click ENTER and drag down the Fill Handle.

The combined text strings will be displayed. The Wrap Text and the AutoFit Row Height options are then used.

The joined text strings with line breaks will be displayed.

Read More: How to Put Multiple Lines in Excel Cell

Method 4 – Using the CONCATENATE Formula in DAX and Power Pivot to Add New Lines

This is the dataset.

Steps:

Go to the Insert Tab >> PivotTable.

In the PivotTable from table or range dialog box, select the data range and click New Worksheet.
Check Add this data to the Data Model and click OK.

A new sheet PivotTable1 and PivotTable Fields will be displayed.

Right-click Range and choose Add Measure.

In the Measure wizard, enter Measure Name (here, Combined) and enter the following formula.

=CONCATENATEX('Range','Range'[List],"
")

Here, Range is the table name, List the name of the column in which information is gathered. A line break was used as a delimiter by pressing ENTER.

Click OK.

The measure name Combined is displayed in Range.

Drag down Name to Rows and Combined to Values.

Go to the PivotTable Analyze Tab >> Grand Totals Group >> Off for Rows and Columns to skip Grand Total

The combined text strings will be displayed. Use the Wrap Text option to see the line breaks.

Select the range of the Combined column and go to the Home Tab >> Alignment Group >> Wrap Text.

The table with the combined texts, each in new lines, will be displayed.

Method 5 – Using the Power Query to Add a New Line

This is the dataset.

Steps:

Go to the Data Tab >> Get & Transform Data >> From Table/Range.

In the Create Table wizard, select the data range and click My table has headers.
Click OK.

The Power Query Editor window will be displayed.

Click the formula symbol.

Enter the following formula.

= Table.AddColumn(#"Changed Type", "Combined", each Text.Combine({[Name],[Street Address],[State]},"#(lf)"))

Here, Combined is the new column name, {[Name],[Street Address],[State]} are the names of the columns to be added, and “#(lf)” is the delimiter for line breaks.