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.
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.
- Pressing Alt+F11 will open Microsoft Visual Basic where we insert a Module from the Insert tab to open a whiteboard.
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.
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.