How to Add New Line with CONCATENATE Formula in Excel (5 Ways)

If you are looking for ways to add a new line in Excel using CONCATENATE formula, then you will find this article useful. So, let’s get started with the main article.

Download Workbook


5 Ways to Add New Line CONCATENATE Formula in Excel

Here, we have a list of street addresses, and states of some employees of a company. We will try to combine them with new lines for each entity by using the following 5 methods.

new line in Excel concatenate formula

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using CONCATENATE Formula to Add New Line

Here, we will use the CONCATENATE function to add the name of the employees with their corresponding Street addresses and states in the Combined column, and using the CHAR function we will enter a line break to start each information in a new line.

new line in Excel concatenate formula

Steps:
➤ Type the following formula in cell 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 these entities and CONCATENATE will join them together with line breaks.

CONCATENATE function

➤ Press ENTER and drag down the Fill Handle tool.

CONCATENATE function

Then, you will see the following combined strings but unfortunately, no line breaks are not appearing here. To make them visible we have to do an extra step here by enabling the Wrap Text option and then autofitting the row heights.

new line in Excel concatenate formula

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

CONCATENATE function

We have wrapped the strings up but they are not fully clear yet and so we have to increase the row heights now to accommodate the strings.

new line in Excel concatenate formula

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

CONCATENATE function

Finally, you will get the combined text strings added in new lines in the Combined column.

CONCATENATE function

Read More: How to Add a Line in Excel Cell (5 Easy Methods)


Method-2: Adding a New Line with Ampersand Operator

In this section, we are going to use the Ampersand operator with the CHAR function to join the names with street addresses and states in new lines.

new line in Excel concatenate formula

Steps:
➤ Type the following formula in cell E4.

=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 these entities and Ampersand (&) will join them together with line breaks.

Ampersand Operator

➤ Press ENTER and drag down the Fill Handle tool.

Ampersand Operator

Then, you will get the following combined texts but with no visible new lines. To show the new lines click on the Wrap Text option and then AutoFit the Row Height option.

new line in Excel concatenate formula

Eventually, you will get the following concatenated text strings each starting in new lines.

Ampersand Operator

Read More: Excel VBA: Create New Line in MsgBox (6 Examples)


Similar Readings


Method-3: Using TEXTJOIN Function

Here, we will be using the combination of the TEXTJOIN function with the CHAR function to add the entities of the Name, Street Address, and State columns in new lines in the Combined column.

new line in Excel concatenate formula

Steps:
➤ Write down the following formula in cell E4.

=TEXTJOIN(CHAR(10),TRUE,B4,C4,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 these entities and TEXTJOIN will join them together with line breaks.

TEXTJOIN function

➤ Press ENTER and drag down the Fill Handle tool.

TEXTJOIN function

After that, we will have the following combined text strings and now we will apply the Wrap Text option and AutoFit Row Height option here.

TEXTJOIN function

Finally, you will visualize the joined text strings with line breaks for each entity.

new line in Excel concatenate formula

Read More: New Line in Cell Formula in Excel (4 Cases) 


Method-4: Using CONCATENATE Formula in DAX and Power Pivot to Add New Line

Here, we are going to use the PivotTable option and then a DAX formula in Power PivotTable to combine the text strings with line breaks. To do this we have to rearrange our previous dataset like below. Here we have listed the names, street addresses, and states for each person serially in the List column and the corresponding name of the listed information has been written in the Name column. The three different colors in the following table indicate the information for three different people Michael, Howard, and Jefferson.

new line in Excel concatenate formula

Steps:
➤ Go to the Insert Tab >> PivotTable Option.

Power pivot

Then, the PivotTable from table or range dialog box will appear.
➤ Select the data range and then click on the New Worksheet option.
➤ Check the option Add this data to the Data Model and press OK.

Power pivot

Then, you will be taken to a new sheet where you will have two portions; PivotTable1, and PivotTable Fields.

new line in Excel concatenate formula

➤ Right-click on the table name Range, then click on the Add Measure option.

Power pivot

After that, the Measure wizard will pop up.
➤ Type the Measure Name (here, we have used Combined) and then write down the following formula in the Formula box.

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

Here, Range is the table name, List is the name of the column in which we gathered all of the information. And notice that as a delimiter we have used the space as a line break by pressing ENTER after the first inverted comma.

➤ Press OK.

Power pivot

Afterward, you will see the created measure name Combined as a field name under the table name Range.

new line in Excel concatenate formula

➤ Drag down the Name field to the Rows area and the Combined field to the Values area.

Power pivot

➤ To disappear Grand Total go to the PivotTable Analyze Tab >> Grand Totals Group >> Off for Rows and Columns Option.

Power pivot

Then, you will get the following outlook of the combined text strings and to show the line breaks we have to enable the Wrap Text option.

Power pivot

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

Power pivot

Ultimately, the following table with the combined texts each in new lines will be visible to you.

new line in Excel concatenate formula

Read More: How to Go to Next Line in Excel Cell (4 Simple Methods) 


Method-5: Using Power Query to Add New Line

Here, we will discuss the use of the Power Query option to add new lines while concatenating text strings.

new line in Excel concatenate formula

Steps:
➤ Go to the Data Tab >> Get & Transform Data Group >> From Table/Range Option.

Power Query

After that, the Create Table wizard will appear.
➤ Select the data range and then click on the My table has headers option.
➤ Press OK.

Power Query

After that, you will be in the Power Query Editor window.

new line in Excel concatenate formula

➤ To add a new step click on the indicated function symbol.

Power Query

➤ Add the following formula to the formula bar.

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

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

Power Query

➤ After pressing ENTER, you will have the combined texts in new lines in the Combined column.

new line in Excel concatenate formula

➤ To close this window, go to the Home Tab >> Close & Load Group >> Close & Load Option.

Power Query

In this way, the table in the Power Query Editor window will be loaded to a new sheet named Table2.

Power Query

Read More: How to Enter within a Cell in Excel (5 Methods)


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice


Conclusion

In this article, we tried to show the ways to add a new line in the Excel CONCATENATE formula easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo