6 Ways to Concatenate with Carriage Return in Excel Formula

Excel is a powerful tool for organizing and analyzing data. One of its most useful features is the ability to concatenate text from multiple cells into one cell. However, sometimes you may want to add a new line or a carriage return between the text for better readability and organization. For example, if you are creating a mailing list and you want to combine the name and address in one cell but separate them with a new line for easy reading. This article will explain 6 ways to concatenate with a carriage return in Excel using formula, so you can effectively organize and present your data.

Concatenate with carriage return using Formula


📁 Download Excel File

Download this file to practice.


What is Carriage Return?

In Excel, a carriage return is a special character that is used to start a new line within a cell. Pressing Alt + Enter on the keyboard will put a carriage return in a specific place. This allows you to enter multiple lines of text within a single cell rather than having to use multiple cells. When many cell entries are combined into one cell, the content of the entire cell might often be too long to show properly. In order to move the contents of the cell to a new line underneath the previous one, we use carriage returns.

Carraige return


Learn to Insert Carriage Return to Concatenate in Excel Formula with These 6 Examples

Today we will learn two things.  At first, we will concatenate the texts of multiple cells into one cell and then put carriage return in particular place in Excel using formula. We will use functions like CHAR, CONCATENATE, TEXTJOIN, and Ampersand operator (&) to get our job done. In addition, we will attempt to write a new function in the VBA script that will only concatenate for our specific case. Not only that, but also we will attempt to do everything in one swoop using VBA with another code. We will explain the functions in their specific examples. I hope today’s lesson will be useful to you.

Dataset to Concatenate with carriage return using Formula

Example 1

1. Using Ampersand Operator & CHAR Function

Ampersand (&) is used to join numerous entries together. All of the cell contents are combined into one cell using the Ampersand operator (&). And the CHAR function is utilized because we wish to add a Carriage Return (Line Break) to the calculation. When a valid character code is provided, the CHAR function returns a character. Characters that are difficult to input in a formula can be specified using the CHAR keyword. This function only comprehended integers 0-255 and was created to function in an ASCII/ANSI system. Remember that the character code for a line break is CHAR (10).

Now let’s go make address cards using the contents of several distinct columns.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want to concatenate your data. We are selecting F6.
  • Now, in the Formula Bar, type this formula to join the contents together.

=B6&CHAR(10)&C6&CHAR(10)&D6&CHAR(10)&E6

Ampersand and CHAR functions to Concatenate with carriage return using Formula

  • After you press Enter, you will notice no line break or carriage return is present on the concatenated result. Also, the result doesn’t fit in one cell. It’s because CHAR(10) only puts a location as to where the carriage return should be placed. So, to make it visible, we need to use the Wrap Text feature from Home tab.

formula result

  • So first, use the Fill Handle to apply the formula to all rows.

Fill Handle to copy formula

  • Finally, select cell F6:F10 again and check the Wrap Text under the “Alignment” group of the Home. You can also use the AutoFit Row Height option from Format feature of the Cells group if necessary.

Wrap text and Autofit to Concatenate with carriage return using Formula

As you can see, now the content looks much more appealing.

Example 1 final output to Concatenate with carriage return using Formula

📕 Read More: 11 Easy Ways to Concatenate Rows in Excel

Example 2

2. Applying CONCATENATE & CHAR Functions

If you don’t want to use the Ampersand operator (&), you can use the CONCATENATE function instead. The result of the CONCATENATE function, which may combine up to 30 items, is returned as text. It accepts up to 30 text-based parameters, such as text1, text2, text3, etc. Values can be provided as hard-coded text strings or cell references. As for the carriage return(Line Break), we will use CHAR(10) just like the previous example, meaning we will nest CHAR(10) inside CONCATENATE function.

Now let’s go create an Excel formula to make address cards using the CONCATENATE function and put carriage return in between texts.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want to concatenate your data. We are selecting F6.
  • Now, in the Formula Bar, type this formula to join the contents together.

=CONCATENATE(B6,CHAR(10),C6,CHAR(10),D6,CHAR(10),E6)

CONCATENATE function to Concatenate with carriage return using Formula

  • After you press Enter, you will notice no line break or carriage return is present on the concatenated result. Also, the result doesn’t fit in one cell. It’s because CHAR(10) only puts a location as to where the carriage return should be placed. So, to make it visible, we need to use the Wrap Text feature from Home tab.

CONCATENATE formula result

  • So, select cell F6 again and check the Wrap Text under the “Alignment” group of the HomeYou can also use the AutoFit Row Height option from Format feature of the Cells group if necessary.

Wrapping text and autofit row height

As you can see, now the content looks much more appealing.

Wrapped text with carriage return

  • Finally, use the Fill Handle to apply the formula and formatting to all rows.

Example 2 final output

📕 Read More: 5 Easy Methods to Combine Rows into One Cell in Excel

Example 3

3. Employing TEXTJOIN & CHAR Functions

Another function similar to CONCATENATE function, which joins multiple cells’ content into one, is TEXTJOIN function. With or without a delimiter, this function concatenates many values together. With the option to disregard empty cells, TEXTJOIN may concatenate values supplied as cell references, ranges, or constants. Delimiter, ignore_empty, and text1 are the three prerequisite parameters for the TEXTJOIN function.

A delimiter, also known as the text to be used between values that are concatenated together, is the text that should be surrounded by double-quotes (“”), such as a space (” “) or a comma with a space (“, “). Provide an empty string to use no delimiter (“”). Empty values can either be ignored or included in the result by setting the Boolean variable Ignore_empty to TRUE or FALSE. To prevent delimiters with no content in the TEXTJOIN result, this is frequently set to TRUE. The first value to be combined is Text1. This might be a hard-coded text value, a range, or a cell reference. Text2, Text3, Text4, and any optional parameters can each have a maximum of 252 values. Since our delimiter will be the carriage return, we will put CHAR(10) in the delimiter section of the function.

Now let’s go make address cards using the TEXTJOIN function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want to concatenate your data. we are selecting F6.
  • Now in the Formula Bar, type this formula to join the contents together.

=TEXTJOIN(CHAR(10),TRUE,B6,C6,D6,E6)

TEXTJOIN function to Concatenate with carriage return using Formula

  • After you press Enter, you will notice no line break or carriage return is present on the concatenated result. Also, the result doesn’t fit in one cell. It’s because CHAR(10) only puts a location as to where the carriage return should be placed. So, to make it visible, we need to use the Wrap Text feature from Home tab.

TEXTJOIN formula result

  • So, select cell F6 again and check the Wrap Text under the “Alignment” group of the Home. You can also use the AutoFit Row Height option from Format feature of the Cells group if necessary.

wrap text and autofit to put carriage return

As you can see, now the content looks much more appealing.

Wrapped text

  • Finally, use the Fill Handle to apply the formula and formatting to all rows.

Example 3 final output

📕 Read More: 5 Easy Ways to Add Comma in Excel to Concatenate Cells

Example 4

4. Utilizing Keyboard Shortcut

If you dislike using functions, you can still concatenate and insert carriage returns the old-fashioned way by using the keyboard shortcut. While this method is easy to remember, it is also tiresome if you are applying it to multiple cells. You have to put the carriage return manually into every concatenated cell. However, if you are a fan of the keyboard, this method is for you.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want to concatenate your data. we are selecting F6.
  • Now in the Formula Bar, type this formula to join the contents together.

=B6&C6&D6&E6

Formula to concatenate

  • After you press Enter, you will notice no line break or carriage return is present on the concatenated result.

Concatenated texts

Fill Handle to apply formula

  • As we want to put the carriage return manually we need only the concatenated texts. So to get rid of the formula, we will select the range F6:F10, press Ctrl+C and subsequently press Ctrl+V. Then from Paste menu, select Paste Values. Now, if you click on any cell, you will notice that the concatenated texts replaced the formula.

Pasting Concatenated texts

  • Now to put carriage return, we will double-click cell F6, and we shall place our cursor at the place where we want our carriage return. Then press Alt+Enter.

Alt+Enter to put carriage return

  • Repeat this Alt+Enter technique for every place you want a line break. It should look like this when you finish.

Repeat Alt+Enter

  • Repeat the process for every cell manually. Your final output should look like this.

Example 4 final output

📕 Read More: 7 Ways to Concatenate Multiple Cells with Space in Excel

Example 5

5. Applying Power Query Tool

Excel has a business intelligence tool called Power Query that enables you to import data from several sources and then clean, modify, and restructure the data as necessary. It enables you to create a query once and reuse it with a straightforward refresh. It has a decent amount of functionality. Power Query can import and clean millions of rows into the data model for further analysis. The user interface is clear and well-designed, making it simple to learn. The good thing about it is that none of it requires you to know or use any code. We can also create a new column and concatenate the existing ones onto the new one using the Power Query Tool.

Now let’s go make address cards using the Power Query Tool.

⬇️⬇️ STEPS ⬇️⬇️

  • Choose the range where you wish the “carriage return” to appear. We will select the range B5:E10. That’s right; we will select the header as well.
  • Next, go to the Data tab and select the From Table/Range option under the Get & Transform Data group.

Accessing Power quesry tool to Concatenate with carriage return using Formula

  • Since our selected range was not in a Table format, Excel will first convert it into a table. First Row is the header so to recognize that we need to check the box behind My Table has headers. Then press OK.

creating table to Concatenate with carriage return using Formula

  • Next, Power Query Editor dialogue box will show up.Since we need to add another column to make the Address Book, we will select Custom Column from General group of Add Column tab.

Adding Custom column

  • In the Custom Column window, Type the Name of the new column in the New column name. We will write “Address Book”. Next, in the Custom column formula box, insert the columns which you want to concatenate in the new column. First, select the column name from the Availabe columns box and then press <<Insert. Next, put an ampersand operator(&) before you insert the next column. After you are finished, press OK.

custom column

As a result, the contents of each column have been joined together, as shown in the image.

New custom column with concatenated texts

  • Next, paste the following formula with a carriage return into the Custom Column Formula box.
= Table.AddColumn(#"Changed Type", "Address Book", each Text.Combine(Record.ToList(_),"#(lf)"))

changing Formula

  • Press Enter to put the carriage return in place.

Concatenate with carriage return using power query

  • Finally, to load the new column, select Close & Load from Home tab.

closing power query tool

The new Table with the concatenated column will appear on a new worksheet.

Loading output

Example 6

6. VBA Code

VBA, or Visual Basic for Applications, is a programming language that is integrated into Microsoft Excel. It enables Excel users to automate repetitive tasks, create custom functions and macros, and even create custom user interfaces within Excel. It is a powerful tool that can help increase productivity and streamline workflows, allowing users to perform complex calculations and data analysis quickly and efficiently. With VBA, users can create custom solutions that are tailored to their specific needs, and can even integrate Excel with other applications. Overall, VBA is a powerful tool that can help users get more out of Excel and automate repetitive tasks.

Today we will create a custom function called ConcatenateCells that will concatenate a certain range of cells and put carriage return between texts. Not only that, but we’ll write code to perform three tasks at once, such as concatenating cells, inserting carriage returns, and finally wrapping the texts.

VBA

6.1. Creating a Function

You can also concatenate multiple columns using a VBA script. We will write a code to create a new Function ConcatenateCells just for this particular task. As usual, we will use CHAR(10) in place of our line break. Let’s use the VBA script to write our code.

⬇️⬇️ STEPS ⬇️⬇️

  • Pressing Alt+F11 will open Microsoft Visual Basic where we insert a Module from the Insert tab to open a whiteboard.

Inserting module to write function code

  • Now, copy and paste the following code into the module.
Function ConcatenateCells(rng As Range) As String
    Dim cell As Range
    Dim result As String
    For Each cell In rng
        result = result & cell.Value & Chr(10)
    Next cell
    ConcatenateCells = Left(result, Len(result) - 2)
End Function

ConcetenateCells code to Concatenate with carriage return using Formula

  • Now that we have created our function let’s use it in Formula Bar. Just type this in the Formula Bar. You don’t have to write the whole function. After that, insert the following formula in the Formula Bar.
=ConcatenateCells(B6:E6)

writing formula to Concatenate with carriage return

  • Next press Enter to see the concatenated texts in cell F6.

formula result

  • Then, select cell F6 again and check the Wrap Text under the “Alignment” group of the Home. You can also use the AutoFit Row Height option from Format feature of the Cells group if necessary.

Wrap text and autofit to Concatenate with carriage return using Formula

As you can see, now the content looks much more appealing.

vba function output

  • Finally, use the Fill Handle to apply the formula and formatting to all rows.

Example 6.1 final output

VBA

6.2. Creating a Command

If you are bored of using functions to concatenate texts, wrapping texts, and then using Fill Handle, we can skip all that by writing a simple code that will do all that for your whole dataset. Let’s use the VBA script to write our code.

⬇️⬇️ STEPS ⬇️⬇️

  • Pressing Alt+F11 will open Microsoft Visual Basic where we insert a Module from the Insert tab to open a whiteboard.

inserting module to Concatenate with carriage return using Formula

  • Now copy and paste the following code into the module.
Sub ConcatenateAndWrap()
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
For i = 6 To lastRow
    ActiveSheet.Cells(i, 6).Value = ActiveSheet.Cells(i, 2).Value & Chr(10) & ActiveSheet.Cells(i, 3).Value & Chr(10) & ActiveSheet.Cells(i, 4).Value & Chr(10) & ActiveSheet.Cells(i, 5).Value
    ActiveSheet.Cells(i, 6).WrapText = True
Next i
End Sub
  • Just press F5 and Magic. You have a perfect Address Book, and you didn’t break a sweat.

VBA code output


📄 Important Notes

📌 The Ampersand Operator(&) connects cell content, so if you want to keep space between contents use them like this: &“ ”&.

📌 The keyboard method has to be repeated for every concatenated cell.

📌 To make the results from the Power Query Tool look their best, use Wrap Text after loading.


📝 Takeaways from This Article

🖊️ Firstly, we explained the definition of carriage return and what it does.

🖊️ Next, we used Ampersand operator(&) and CHAR(10) function to concatenate texts and insert carriage returns.

🖊️ Then, we also demonstrated how users can apply CONCATENATE, and CHAR(10) functions to make an Excel formula to concatenate texts and insert carriage return.

🖊️ We then move on to explaining TEXTJOIN and CHAR(10) functions’ capability to do the same.

🖊️ Then, we gave a demonstration of how the keyboard can play a role here.

🖊️ Again, we showed how Power Query tool can do the same tasks.

🖊️ Lastly, we created a custom function and code for every task of today’s lesson in one go using VBA script.


Conclusion

In conclusion, adding a carriage return or new line within concatenated text in Excel can improve the readability and organization of your data. It is important to understand the different methods and when to use them to make the most of Excel’s powerful data manipulation capabilities. With the knowledge of these methods, you can effectively organize and present your data in a clear and concise manner. I hope with from now on you will be comfortable to concatenate texts and inserting carriage return using Excel formula. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.


Related Articles

(Visited 50 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo