Carriage Return in Excel Formula to Concatenate (6 Examples)

Method 1 – Using Ampersand Operator and CHAR Functions Insert Carriage Return in Excel Formula to Concatenate

Step 1: Insert the following formula into any adjacent cell (i.e., H5).

=B5&CHAR(32)&C5&CHAR(10)&D5&CHAR(44)&E5&CHAR(44)&F5&CHAR(44)&G5

Ampersand-Carriage Return in Excel Formula to Concatenate

➤ Press ENTER. The cell entry does not show any carriage return.

Formula insertion

Step 2: To make the carriage return visible, go to the Home tab > Select the Wrap Text option (in the Alignment section).

Wrap text

➤ Selecting Wrap Text makes the cell contents appear in the desired format. H5 cell content appear with a carriage return.

Carriage Return

Step 3: Drag the Fill Handle to all the cells.

Ampersand result-Carriage Return in Excel Formula to Concatenate

Read More: How to Combine Two Formulas in Excel


Method 2 – CONCATENATE and CHAR Functions to Insert Carriage Return

Step 1: Paste the following formula in any blank cell (i.e., H5).

=CONCATENATE(B5,CHAR(32), C5,CHAR(10),D5,CHAR(44),E5,CHAR(44),F5,CHAR(44),G5)

Concatenate -Carriage Return in Excel Formula to Concatenate

Step 2: Repeat Step 2 of Method 1 to wrap the texts and use the Fill Handle to display all the joined contents with a carriage return.

Concatenate-Carriage Return in Excel Formula to Concatenate

Read More: How to Copy CONCATENATE Formula in Excel


Method 3 – TEXTJOIN Function to Places Carriage Return in Concatenated Texts

Step 1: Add the following formula in any adjacent blank cell (i.e., H5).

=TEXTJOIN(CHAR(10),FALSE,B5&CHAR(32)&C5,D5&CHAR(44)&E5&CHAR(44)&F5&CHAR(44)&G5)

Textjoin-Carriage Return in Excel Formula to Concatenate

Step 2: Hit the ENTER key and drag the Fill Handle to apply the formula and cell format to other cells.

Textjoin Result-Carriage Return in Excel Formula to Concatenate

Read More: How to Concatenate Names in Excel


Method 4 – Keyboard Shortcuts to Insert Carriage Return

Step 1: Use the Ampersand formula to concatenate the texts.

=B5&CHAR(32)&C5&D5&CHAR(44)&E5&CHAR(44)&F5&CHAR(44)&G5

Formula

Step 2: To insert the joined text values as just values, Right Click on the text values > Select Copy (from the Context Menu options).

Copy

Step 3: Select the entire range and Right Click on them. Select the Paste Options Value above the Paste Special feature.

Paste

Step 4: Pasting the texts as value removes the formula and makes them plain text. Place the Cursor anywhere within the joined text string (i.e., after the Last and First Name). Press ALT+ENTER.

as text

➤ Pressing ALT+ENTER inserts a line break (a carriage return) after Full Name, making the cell content more user-friendly.

Inserted carriage return

➤ Drag the Fill Handle to copy the carriage return to all the other cells.

keyboard result-Carriage Return in Excel Formula to Concatenate

Read More: How to Concatenate Date and Time in Excel


Method 5 – VBA Macro Custom Function to Join the Entries with Carriage Return

Step 1: Press ALT+F11 to open the Microsoft Visual Basic window. In the window, Select Insert (from the Toolbar) > Choose Module. The Module window appears.

Module insertion

Step 2: In the Module window, paste the following VBA Macro Code to generate a custom formula.

Function CrgRtrn(name As String, address As String) As String
CrgRtrn = name & Chr(10) & address
End Function

Macro code

Step 3: Return to the worksheet and type =Cr in the formula bar. The custom function appears below the Formula bar. Double Click on the function.

Formula insertion

Step 4: Assign the cell reference as shown below.

=CrgRtrn(B5,C5)

Formula insertion-Carriage Return in Excel Formula to Concatenate

Step 5: Press ENTER to concatenate the texts. The carriage return is not visible.

Result

Step 6: To display the carriage return, select the Wrap Text option from the Home tab.

Carriage return

➤ Drag the Fill Handle to display the carriage return in all the cells.

Carriage Return in Excel Formula to Concatenate

Read More: How to Combine Names in Excel with Space


Method 6 – Power Query Combines Entries with a Carriage Return Delimiter

Step 1: Select the range where you want to place the carriage return. Go to the Data tab > Select From Table/Range option (in the Get & Transform Data section).

power query-Carriage Return in Excel Formula to Concatenate

Step 2: If your dataset is not in a Table format, the selection converts it into a Table. Click OK in the Create Table dialog box.

Table

Step 3: The Power Query Editor window appears. In the window, Select Add Column (from the ribbon) > Choose Custom Column (from the General section).

Power query editor

Step 4: The Custom Column command box appears. In the box, Give a name to the new column. Insert the available columns in the Custom Column formula box and join them with an Ampersand.

Custom column-Carriage Return in Excel Formula to Concatenate

It inserts a custom column beside the existing columns concatenating the text within both columns.

Step 5: In the Custom Column formula box, paste the following formula to place a carriage return between the Name and Address columns.

= Table.AddColumn(#"Changed Type", "Address Labels", each Text.Combine(Record.ToList(_),"#(lf)"))

Formula insertionStep 6: Hit ENTER to insert carriage return to all the entries in the Custom Column.

Formula result-Carriage Return in Excel Formula to ConcatenateStep 7: Go to Home tab > Select Close & Load (from the Close & Load section).

Close and Load data

➤ The Close & Load command inserts the entries in a new Excel worksheet as shown in the following screenshot. The carriage return is not visible.

loaded data

Step 8: To make the line break or carriage return visible, select the entire custom column range and apply Wrap Text.

Formatted data-Carriage Return in Excel Formula to Concatenate

Read More: How to Combine Name and Date in Excel


Download Excel Workbook


Related Articles


<< Go Back to Excel Concatenate TextConcatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo