Tanjim Reza
Sometimes, our Excel data is extracted or built in an improper format. It occurs very frequently that we get data with multiple lines containing carriage returns. Now, for proper data format and visualization, it is a must to remove carriage return and split the text into multiple columns in Excel. In this article, I will show you () methods to remove carriage return in Excel text to columns.
Download Practice Workbook
You can download our practice workbook from here for free!
What Is Carriage Return?
Carriage return is mainly a special character that is used to create a new line of a text and continue the text from the beginning of the new line. It is also commonly known as cartridge return, return, and CR.
It is generally created by hitting the Enter button. But, in the Excel cell, you need to press Alt + Enter to create a carriage return.
Carriage return gives fresher look to the data, explanation, visualization, etc. You can start a new para with this character very easily.
Steps to Remove Carriage Return with Text to Columns Feature in Excel
Say, we have an information set of 5 people containing their names, professions, and countries. This information is written in one cell with a carriage return. Now, we want to remove carriage return in Excel text to columns. You can follow the simple steps below to achieve this.
📌 Step 1: Access the Text to Columns Tool
First, you need to access the Text to Columns tool properly.
- To do this, at the very beginning, select the information set (B5:B9 here).
- Subsequently, go to the Data tab >> Data Tools group >> Text to Columns tool.
Read More: How to Find Carriage Return in Excel (2 Easy Methods)
📌 Step 2: Finish ‘Convert Text to Columns Wizard’ Setup
- Now, the Convert Text to Columns Wizard – Step 1 of 3 dialogue box will appear.
- Subsequently, put the radio button on the Delimited option and click on the Next button.
- Afterward, the Convert Text to Columns Wizard – Step 2 of 3 window will appear.
- Here, check on the Other: option’s checkbox only.
- Following, press Ctrl + J in the text box beside the Other: option.
- Last but not the least, click on the Next button.
- Now, the Convert Text to Columns Wizard – Step 3 of 3 dialogue box will appear.
- Now, choose the General option from the Column data format group.
- Following, put the reference as D5 in the Destination: text box.
- Finally, click on the Finish button.
- As a result, a Microsoft Excel dialogue box will appear warning you about data replacement.
- Click on the OK button.
Thus, you can remove carriage return in Excel text to columns. And, for example, the output should look like this.
Read More: How to Insert Carriage Return in Excel Cell (3 Simple Ways)
Remove Carriage Return in Excel with a VBA Code
You can also use VBA code to remove carriage return and split your information into pieces at different cells. Follow the steps below to accomplish this using VBA code.
📌 Steps:
- First and foremost, go to the Developer tab >> Visual Basic tool.
- At this time, the Microsoft Visual Basic for Applications window will appear.
- Click on Sheet3 from the VBAProject group.
- Subsequently, a code window will appear for this sheet.
- Afterward, write the code below in the code window.
- Following, press Ctrl + S to save the code.
- Now, a Microsoft Excel window will appear about the confirmation of saving.
- Click on the No button.
- As a result, the Save As dialogue box will appear.
- Now, choose the Save as type: option as .xlsm file and click on the Save button.
- Afterward, close the code window.
- Following, select the cell where you want the split >> go to the Developer tab >> Macros tool.
- As a result, the Macros window will appear.
- Choose the RemoveCarriageReturn macro from the list and click on the Run button.
- At this time, the Remove Carriage Return window will appear.
- Write B5 in the Range text box.
- Finally, click on the OK button.
As a result, the B5 cell’s carriage return is removed and the data are split. For an instance, the outcome should look like this now.
💬 Things to Remember
- You can insert a carriage return in a cell by pressing Alt + Enter in the selected cell.
- It is better to turn on the Wrap Text option from the Excel Home tab for seeing the full data with carriage returns.
- You can use Alt + 0010 from the numeric keyboard instead of Ctrl + J in the Other: text box.
Conclusion
In a nutshell, in this article, I have shown you step-by-step guidelines to remove carriage return in Excel text to columns. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative.
Moreover, you are very welcome to comment here if you have any further questions or recommendations.
And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
