Carriage Return in Excel Formula to Concatenate (6 Examples)

Often, we insert Carriage Return in Excel formula concatenate to make our data more comprehensible. We can insert a Carriage Return in formula concatenate using various functions such as CONCATENATE, CHAR, TEXTJOIN as well as VBA Macro, Ampersand (&), and Power Query features.

Let’s say we have Last Name, First Name, Business Address, City, State, and Zip Code columns in a dataset. And we want to insert a Carriage Return in concatenate formulas’ outcomes. In this article, we demonstrate the ways to insert carriage return in Excel formula concatenate.

Dataset-Carriage Return in Excel Formula to Concatenate


Download Excel Workbook


What is Carriage Return?

In Excel, Carriage Return is an operation to move part of the cell contents to a new line within the cell. In cases, when multiple cell entries get concatenated in one cell, the entire cell content becomes too lengthy to display comfortably. As a result, carriage returns are inserted to push the cell contents to a new line under the previous one.

carriage return-Carriage Return in Excel Formula to Concatenate


6 Easy Ways to Insert Carriage Return in Excel Formula to Concatenate

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

In order to make a Name and Address column, we have to concatenate multiple columns entries into one. To concatenate multiple entries, we use Ampersand (&). But as we want to insert a Carriage Return into the formula, the CHAR function is used. We know CHAR (10) is the character code for Line Break.

Step 1: Type 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

The Ampersand Operator (&) joins all the cell contents as you instruct it in the formula. The formula joins Last and First Name with a Space Character (i.e., CHAR (32)) (i.e., B5&CHAR (32) &C5).

It joins the full address with Comma Character (i.e., CHAR (44)) (i.e., D5&CHAR (44) &E5&CHAR (44) &F5&CHAR (44) &G5).

At last, both Name and Address portions get separated by a Line Break or Carriage Return (i.e., CHAR (10)).

Ampersand-Carriage Return in Excel Formula to Concatenate

➤ After inserting the formula, press ENTER. You see no carriage return among the individual entries as depicted in the image below.

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. Now, you can see H5 cell contents appear with a carriage return.

Carriage Return

Step 3: Drag the Fill Handle to appear all the cell contents in the desired format as shown in the picture below.

Ampersand result-Carriage Return in Excel Formula to Concatenate

Read More: Carriage Return in Excel Formula to Concatenate (6 Examples)


Method 2: CONCATENATE and CHAR Functions to Insert Carriage Return

The CONCATENATE function also joins multiple entries in one cell. In this section, we use the CONCATENATE function to concatenate the individual cell entries. But in order to place a carriage return, we have to infuse the CHAR function in the CONCATENATE formula.

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)

The joining pattern for the entries is similar as we explain in Step 1 of Method 1.

Concatenate -Carriage Return in Excel Formula to Concatenate

Step 2: Following the formula insertion, 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 Concatenate in Excel (3 Suitable Ways)


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

Similar to Method 1 and 2, the TEXTJOIN function concatenates multiple cell entries and displays them in one cell. The syntax of the TEXTJOIN function is

TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...)

Here,

delimiter; the separator between texts.

ignore_empty; offer two options whether it ignores the blank cells or not. TRUE for ignoring the blank or empty cell and FALSE otherwise.

Step 1: Write 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)

Inside the formula,

CHAR (10); is the Carriage Return delimiter.

FALSE; is the ignore_empty option.

B5&CHAR (32) &C5 = text1

D5&CHAR (44) & E5&CHAR (44) &F5&CHAR (44) &G5 = text2.

Textjoin-Carriage Return in Excel Formula to Concatenate

Step 2: Hit the ENTER key then 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: Combine Text in Excel (8 Suitable Ways)


Similar Readings:


Method 4: Keyboard Shortcuts to Insert Carriage Return

Suppose we want to concatenate the cell entries without inserting the Carriage Return. We simply use the Ampersand (&) to join those entries.

In Method 1, we demonstrate how we can transform several texts into one lengthy text value. After joining the texts, we can insert them as values by getting rid of the formula. Then we can place the Carriage Return in the converted cells using Keyboard Shortcuts (i.e., ALT+ENTER).

Step 1: Previously you used Ampersand to concatenate the texts. Similarly use the Ampersand formula to concatenate the texts.

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

The above formula places Space between First and Last Names (i.e., B5&CHAR (32) &C5). Then the formula places Comma between different cell entries while joining them (i.e., D5&CHAR (44) &E5&CHAR (44) &F5&CHAR (44) &G5).

Formula

Step 2: In order 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: Again, after Selecting the entire range 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 that constitutes them. You can see from the below picture that all the joined texts are in plain text. Place the Cursor anywhere within the joined text string (i.e., after the Last and First Name). Press ALT+ENTER altogether.

as text

➤ Pressing ALT+ENTER inserts a line break or you call a carriage return after Full Name. This carriage return separates the address with a line break making the cell content more user-friendly.

Inserted carriage return

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

keyboard result-Carriage Return in Excel Formula to Concatenate

Read More: How to Insert Carriage Return in Excel Cell (3 Simple Ways)


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

Excel VBA Macros are very efficient in achieving desired outcomes. In this method, we demonstrate a custom function generated by a VBA Macro Code to insert a carriage return within a concatenated text string. Therefore, we slightly modify the dataset combining Name and Address into two separate cells. Using a custom function, we try to concatenate the cell entries into one.

vba dataset-Carriage Return in Excel Formula to Concatenate

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

The macro code generates a custom function that has a syntax of name & Chr(10) & address. So, the custom function (i.e., CrgRtrn) inserts a carriage return as instructed in its syntax.

Step 3: By returning to the worksheet, Type =Cr… you see the custom function appears below the Formula bar. Double Click on the function.

Formula insertion

Step 4: Assign the cell reference as depicted in the following picture. As a result, the formula becomes the below one.

=CrgRtrn(B5,C5)

In the formula, B5 and C5 are two strings separated by a carriage return or line break character (i.e., CHAR (10)).

Formula insertion-Carriage Return in Excel Formula to Concatenate

Step 5: Use the ENTER key to concatenate the texts. However, the carriage return doesn’t show up similar to the picture below.

Result

Step 6: To display the carriage return, Select the Wrap Text option from the Home tab. Right then the carriage return appears as shown in the image below.

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 Find Carriage Return in Excel (2 Easy Methods)


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

Excel Power Query is a strong tool to deal with data. We can use a custom column formula to insert carriage return in our dataset.

Step 1: Select the range in where you want to place the carriage return. Afterward, 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: In case your dataset is not in a Table format, the selection converts it into a Table. Click on 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 reasonable 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)"))

 The pasted formula combines records with a delimiter “#(lf)” that inserts a carriage return.

Formula insertionStep 6: Apply the ENTER key and you see all the entries in the Custom Column get carriage return inserted.

Formula result-Carriage Return in Excel Formula to ConcatenateStep 7: Now, we have to load the entries in an Excel worksheet. 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. But there is no carriage return inserted.

loaded data

Step 8: To visible the line break or carriage return, Select the entire custom column range and apply Wrap Text. In a moment all the entries get separated by the desired format (i.e., Carriage Return between Name and Address).

Formatted data-Carriage Return in Excel Formula to Concatenate

Read More: Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)


Conclusion

In this article, we demonstrate ways to insert carriage return in Excel formula to concatenate operations. To achieve this format, we use functions like CONCATENATE, CHAR, TEXTJOIN. Other Excel features such as Ampersand, VBA Macros, and Power Query are also discussed in this article. You can use any of these methods to get the format in shape. Hope these above-described methods are efficient enough to fulfill your demand. Comment, if you have further inquiries or anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo