6 Ways to Concatenate with Carriage Return in Excel Formula

June 19, 2023

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. 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. 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.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.

Download Excel File

Download this file to practice.

Concatenate with Carriage Return.xlsx

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.

As you can see, now the content looks much morine 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.

e “Alignment” gro

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

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

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.

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.

About the author

Pabok

+ posts