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.

How to Add New Line CONCATENATE Formula in Excel: 5 Ways

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.

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.

Steps:
➤ Type the following formula in cell E4.

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.

➤ Press ENTER and drag down the Fill Handle tool.

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.

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

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.

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

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

Read More: How to Enter within a Cell in Excel 

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.

Steps:
➤ Type the following formula in cell E4.

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.

➤ Press ENTER and drag down the Fill Handle tool.

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.

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

Read More: Excel: Inserts New Line in Cell Formula 

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.

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

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.

➤ Press ENTER and drag down the Fill Handle tool.

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.

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

Read More:  How to Put Multiple Lines in Excel Cell 

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.

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

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.

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

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

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.

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.

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

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

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

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.

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

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

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.

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

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

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

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

➤ Add the following formula to the formula bar.

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.

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

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

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

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.

Download Workbook

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

• How to Make Two Lines in One Cell in Excel

<< Go Back to New Line | Text Formatting | Learn Excel